While Sql Server provides functions to manipulate datetime data types(DateDiff, DateAdd etc.) they don't have(as far as I know) functions to manipulate time. Very useful when computing time differences. In the past I had to manually datediff parts of the time(hh:mm:ss) which wasn't scalable especially if you're going to deal with more than a day of difference. However there's an easy way to deal with time differences, regardless of the amount of difference(day, month, year, hours etc.)
Sql Server stores it's datetime datatype as 2 4byte integer, the first portion holds the date and the fraction portion holds the time(ie.. 12345.67890), so to get the time difference:
DECLARE @timeIn AS DATETIME
DECLARE @timeOut AS DATETIME
SELECT @timeIn ='2007/07/25 20:20:30', @timeOut = '2007/07/25 22:30:30'
SELECT CAST(CAST(@timeOut AS float) - CAST(@timeIn AS float) AS DATETIME)
results to:
1900-01-01 02:10:00.000
2 hrs and 10 mins.. which you can easily extract using CONVERT(varchar(10),@timeDiff,114)
hth