MSSQL - Time ?

[Acheleon]

Based on the tables and data below,
how can I calculate the amount of time
between the two columns in hours/secs?

TimeIn [datetime]
2004-08-17 15:52:38.000

TimeOut[datetime]
2004-08-17 17:54:02.000


Thanks

[Rawhide]

Use the DateDiff function.

[MAK]

create table timetest1 (TimeIn datetime,TimeOut datetime)
insert into timetest1 select '2004-08-17 15:52:38.000' , '2004-08-17 17:54:02.000'
insert into timetest1 select '2004-08-13 15:52:38.000' , '2004-08-17 17:54:02.000'
insert into timetest1 select '2004-07-17 15:52:38.000' , '2004-08-17 17:54:02.000'
insert into timetest1 select '2002-07-17 15:52:38.000' , '2004-08-17 17:54:02.000'

select timeout-timein from timetest1

[Acheleon]

Im still not sure what to do here.
This gives me what I want but I do not know how to put this result set into a table or view of its own without using a cursor. Any Ideas?

DECLARE AddHours CURSOR FOR
Select (datediff(hh,TimeIn,TimeOut))
as HrsWorked from TimeTest1
OPEN AddHours
FETCH NEXT FROM AddHours
WHILE @@FETCH_STATUS = 0
FETCH NEXT FROM AddHours
CLOSE AddHours
DEALLOCATE AddHours

[Rawhide]

This is what I was suggesting you use:

Create Table #timetest1 (TimeIn datetime, TimeOut datetime)

Insert Into #timetest1
Values ('2004-08-17 15:52:38.000 ', '2004-08-17 17:54:02.000')

Select DateDiff(hh, TimeIn, TimeOut) As DifferenceInHours,
DateDiff(ss, TimeIn, TimeOut) As DifferenceInSeconds,
DateDiff(ss, TimeIn, TimeOut)%(60 * 60) As LeftOverSeconds
From #timetest1

Drop Table #timetest1

[Acheleon]

That is what I needed, thanks so much.