How to calculate the difference in hours (decimal) between two dates in SQL Server?

Question

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

I could not find any useful method for converting datetime to decimal from "CONVERT" to MSDN.
Can someone help me with this?

UPDATE:
To be clear, I also need the fractional part (thus the decimal type). Therefore, from 9:00 to 10:30 he must return me 1.5.

Answer 1

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

If you need the fractional hours, you can use DATEDIFF with higher resolution and split the result:

 DATEDIFF(second, start_date, end_date) / 3600.0 

The documentation for DATEDIFF is available on MSDN:

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

Answer 2

Just subtract the two datetime values ​​and multiply by 24:

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

The test script could 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 January 1, 1900, and the second integer (representing time) is the number ( 1 ) of ticks from midnight. (For SmallDatetime, the integer time is the number of minutes since midnight). Any arithmetic performed by values ​​uses the time part as part of the day. 6am = 0.25, noon = 0.5, etc. See the MSDN link here for more details.

So Cast ((@ Dt2 - @ Dt1) like Float) gives you the total number of days between two dates. Multiply by 24 to convert to hours. If you need full minutes, several minutes per day (24 * 60 = 1440) instead of 24 ...

NOTE 1 This is not the same as the dotNet or javaScript tick - this tick is about 3.33 milliseconds.

Answer 3

DATEDIFF, but note that it returns an integer, so if you need a fraction of the hour, use something like this: -

 CAST(DATEDIFF(ss, startDate, endDate) AS decimal(precision, scale)) / 3600 

Answer 4

You are probably looking for the DATEDIFF function.

DATEDIFF (datepart, startdate, enddate)

Where you can look like this:

DATEDIFF (hh, startdate, enddate)

Answer 5

 DATEDIFF(minute,startdate,enddate)/60.0) 

Or use this for two decimal places:

 CAST(DATEDIFF(minute,startdate,enddate)/60.0 as decimal(18,2)) 

Answer 6

 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

Answer 7

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