i want Spend Time between TimeIn and TimeOUT one thing more to consider is timein Date and Timeout Date some time is not sameResultant Data Should be like thisID--Date--------TimeIN---------------TimeOUT--------------SPENDTIME---1---2012-01-06--2012-01-06 08:54:09--2012-01-07 01:15:01--16:39:08---2---2012-01-06--2012-01-06 14:00:21--2012-01-07 01:00:21--11:00:00---3---2012-01-06--2012-01-06 08:00:21--2012-01-06 18:00:01--10:00:20---4---2012-01-06--2012-01-06 18:20:21--2012-01-07 08:20:30--14:00:09---

thanks Every one i also want to group result by ID,Datehow can it will be possible

SELECT ID, [date], TimeIN,TimeOUT, RIGHT('0' + CONVERT(varchar(4),DATEDIFF(dd,0,DATEADD(ss,TimeDiffInSec,0)) * 24 + (TimeDIffInSec/3600)), 2) + ':' + RIGHT(CONVERT(varchar(8),DATEADD(ss,TimeDIffInSec,0),108),5) AS timediffFROM(SELECT *,DATEDIFF(ss,timeIN,TimeOUT) AS TimeDIffInSecFROM YOURTABLEGroup by ID,Date) T;but it didnot worksand give following resultTimeIN' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

TimeOUT' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.