views:

1163

answers:

6

I have to calculate the difference in hours (decimal type) between two dates in SQL Server 2008.

I couldn't find any useful technique to convert datetime to decimal with 'CONVERT' on MSDN.
Can anybody help me with that?

UPDATE:
To be clear, I need the fractional part as well (thus decimal type). So from 9:00 to 10:30 it should return me 1.5.

A: 
Declare @date1 datetime
Declare @date2 datetime

Set @date1 = '11/20/2009 11:00:00 AM'
Set @date2 = '11/20/2009 12:00:00 PM'

Select Cast(DateDiff(hh, @date1, @date2) as decimal(3,2)) as HoursApart

Result = 1.00

RandomNoob
DateDiff() returns an int. You could cast to decimal, but it's already truncated the mantissa.
Joel Coehoorn
Good to know, thanks @Joel
RandomNoob
A: 

SELECT DATEDIFF(hh, firstDate, secondDate) FROM tableName WHERE ...

LorettoDave
DateDiff() returns an int. You could cast to decimal, but it's already truncated the mantissa.
Joel Coehoorn
+1  A: 

You are probably looking for the DATEDIFF function.

DATEDIFF ( datepart , startdate , enddate )

Where you code might look like this:

DATEDIFF ( hh , startdate , enddate )

Vincent Ramdhanie
The 'hh' interval won't do what he wants. He needs a smaller interval so he can compute fractional hours.
Joel Coehoorn
+2  A: 

DATEDIFF but note it returns an integer so if you need fractions of hours use something like this:-

CAST(DATEDIFF(ss, startDate, endDate) AS decimal(precision, scale)) / 3600
AnthonyWJones
Downvoter, state your reason??
AnthonyWJones
+6  A: 

DATEDIFF(hour, start_date, end_date) will give you the number of hour boundaries crossed between start_date and end_date.

If you need the number of fractional hours, you can use DATEDIFF at a higher resolution and divide the result:

DATEDIFF(second, start_date, end_date) / 60.0 / 60.0

The documentation for DATEDIFF is available on MSDN:

http://msdn.microsoft.com/en-us/library/ms189794%28SQL.105%29.aspx

Phil Ross
Thanks a lot! You guys are crazy, I posted the question just a few minutes ago and already have a reply :-)
Marc
A: 

Just subtract the two datetime values and multiply by 24:

  Select Cast((@DateTime2 - @DateTime1) as Float) * 24.0

a test script might be:

  Declare @Dt1 dateTime Set @Dt1 = '12 Jan 2009 11:34:12'
  Declare @Dt2 dateTime Set @Dt2 = getdate()

  Select Cast((@Dt2 - @Dt1) as Float) * 24.0

This works because all datetimes are stored internally as a pair of integers, the first integer is the number of days since 1 Jan 1900, and the second integer (representing the time) is the number of millesconds since Midnight. (For SmallDatetimes the time portion integer is the number of minutes since midnight). Any arithmetic done of the values uses the time portyion as a fraction of a day. 6am = .25, noon = 0.5, etc... See MSDN link here for more details.

So Cast((@Dt2 - @Dt1) as Float) gives you total days between two datetimes. Multiply by 24 to convert to hours. If you need total minutes, Multiple by Minutes per day (24 * 60 = 1440) instaed of 24...

Charles Bretana