sql-server - 如何计算两个 datetime2 列之间的间隔 (SQL Server)？

Question

您好，我正在尝试计算 datetime2 类型的两列之间的差异。

但是 SQL Server (2012) 似乎不喜欢以下内容:

select cast ('2001-01-05 12:35:15.56786' as datetime2)
    - cast ('2001-01-01 23:45:21.12347' as datetime2);

Msg 8117, Level 16, State 1, Line 2
Operand data type datetime2 is invalid for subtract operator.

现在，如果我将其转换为日期时间类型，它就可以工作:

select cast (cast ('2001-01-05 12:35:15.56786' as datetime2) as datetime) 
    - cast (cast ('2001-01-01 23:45:21.12348' as datetime2) as datetime);

1900-01-04 12:49:54.443

但是，当我将其转换为日期时间时，我会失去精度(请注意上面的 3 位小数精度)。在这种情况下，我实际上需要全部 5 位小数。有没有办法获得两个 datetime2 列之间的间隔并仍然保持 5 个小数点的精度？谢谢。

Answer 1

您可以简单地使用 DateDiff

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

select DATEDIFF(MILLISECOND, cast('20010101 23:45:21.12347' as datetime2), 
                             cast('20010105 12:35:15.56786' as datetime2))

不幸的是，尝试通过以下方式获得所需的精度:

select DATEDIFF(MICROSECOND, cast('20010101 23:45:21.12347' as datetime2), 
                             cast('20010105 12:35:15.56786' as datetime2)) 

导致溢出错误:

The datediff function resulted in an overflow. 
The number of dateparts separating two date/time instances is too large. 
Try to use datediff with a less precise datepart.

实现您想要的精度的一种方法是迭代地分解粒度时间组件(天、小时、分钟、秒等)，并使用 DateAdd() 从值中减去它，例如

remainingAtLowerGranularity = DateAdd(granularity, -1 * numFoundInStep, value)