如何计算 SQLServer 中两个日期之间的小时差(小数) ？

我必须计算 SQLServer2008中两个日期之间的小时差(小数类型)。

我找不到任何有用的技术来转换日期时间为十进制与’转换’在 MSDN。
有人能帮我吗？

更新:
为了清楚起见，我还需要小数部分(因此是小数类型)。所以从9点到10点半，它应该返回1.5。

298637 次浏览
小开 
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

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

You are probably looking for the DATEDIFF function.

DATEDIFF ( datepart , startdate , enddate )

Where you code might look like this:

DATEDIFF ( hh , startdate , enddate )

小开

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
小开
最佳答案

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) / 3600.0

The documentation for DATEDIFF is available on MSDN:

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

小开

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 (1) ticks since Midnight. (For SmallDatetimes the time portion integer is the number of minutes since midnight). Any arithmetic done on the values uses the time portion as a fraction of a day. 6am = 0.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) instead of 24...

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

小开 
DATEDIFF(minute,startdate,enddate)/60.0)

Or use this for 2 decimal places:

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

Using Postgres I had issues with DATEDIFF, but had success with this:

  DATE_PART('day',(delivery_time)::timestamp - (placed_time)::timestamp) * 24 +
DATE_PART('hour',(delivery_time)::timestamp - (placed_time)::timestamp) +
DATE_PART('minute',(delivery_time)::timestamp - (placed_time)::timestamp) / 60

which gave me an output like "14.3"


提交答案