如何在SQL Server中计算两个日期之间的小时差(十进制)?

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

我必须计算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?

我找不到任何有用的技术来在MSDN上“转换”将datetime转换为decimal。有人能帮我吗?

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.

更新:为了明确，我还需要小数部分(因此需要十进制类型)。从9点到10点30分，它会返回1。5。

8 个解决方案

#1

120

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

DATEDIFF(hour, start_date, end_date)将会给出start_date和end_date之间的小时边界值。

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

如果您需要数小时，您可以使用更高分辨率的DATEDIFF并分割结果:

DATEDIFF(second, start_date, end_date) / 3600.0

The documentation for DATEDIFF is available on MSDN:

DATEDIFF的文档在MSDN上可用:

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

#2

Just subtract the two datetime values and multiply by 24:

减去两个datetime值，再乘以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.

这可以工作，因为所有日期时间在内部存储为一对整数，第一个整数是自1900年1月1日以来的天数，第二个整数(表示时间)是自午夜以来的(1)滴答数。(对于SmallDatetimes，时间部分整数是午夜以后的分钟数)。任何计算值的算法都将时间部分作为一天的一小部分。6am = 0.25, noon = 0.5，等等…更多细节请参见MSDN链接。

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...

所以Cast(@Dt2 - @Dt1)作为浮点数给你在两个日期时间之间的总天数。乘以24转换成小时。如果你需要总分钟，以分钟为单位计算(24 * 60 = 1440)而不是24……

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

注意1:这与dotNet或javaScript标记不同——这个标记大约是3.33毫秒。

#3

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

DATEDIFF但是注意它返回一个整数，所以如果你需要几个小时使用这样的东西:-。

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

#4

You are probably looking for the DATEDIFF function.

您可能正在寻找DATEDIFF函数。

DATEDIFF ( datepart , startdate , enddate )

DATEDIFF (datepart, startdate, enddate)

Where you code might look like this:

代码可能是这样的:

DATEDIFF ( hh , startdate , enddate )

DATEDIFF (hh, startdate, enddate)

#5

DATEDIFF(minute,startdate,enddate)/60.0)

Or use this for 2 decimal places:

或者用这个表示小数点后两位:

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

#6

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

使用Postgres时，我对DATEDIFF有异议，但我成功地做到了:

  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"

它给了我一个输出，比如"14.3"

#7

-1

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

结果= 1.00

#8

-1

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

从tableName中选择DATEDIFF(hh, firstDate, secondDate)。

#1

120