Computing Time Difference

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
 

 

Published 06-23-2007 7:29 PM by bonskijr
Filed under: ,

Comments

# re: Computing Time Difference

You can use DateDiff to extract time information.

e.g.

DateDiff(MS, table.start, table.end) gives you the difference between start+end in milliseconds. Use 'HOUR' to get hours instead, etc.

Tuesday, September 04, 2007 2:55 AM by Anon

# re: Computing Time Difference

you sure can, however if your going to check the difference to the seconds, you'd have to datediff it 3 times(hour,minutes,seconds)...

as I understand katmai will have seperate time datatype so it we won't need hack like this..

Tuesday, September 04, 2007 10:37 PM by bonskijr