SQL Server:datediff 函数导致溢出

问题描述：

这个错误是什么意思，我该如何避免?

What does this error mean and how can I avoid it?

datediff 函数导致溢出.分隔两个日期/时间实例的日期部分数量太大.尝试将 datediff 与不太精确的日期部分一起使用.

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.

我没有使用 datediff 函数.我正在执行此查询，其中 Timestamp 是日期时间类型:

I am not using the datediff function. I am doing this query where Timestamp is a datetime type:

SELECT TOP 10 * from vSomeView 
WHERE TimestampUTC >= '2009-08-13 22:17:00'

我可能做错了什么?

我使用的是 SQL Server 2008.

I'm using SQL Server 2008.

答

SQL Server 可能在内部做 DATEDIFF 比较，如果两个日期相差 68 年以上(并且内部 DATEDIFF 是按秒计算的), DATEDIFF 可能会出错，因为 DATEDIFF 的输出是 INT.

SQL Server may be doing a DATEDIFF internally for the comparison, and if the two dates are much more than 68 years apart (and the internal DATEDIFF is by seconds), DATEDIFF can error as the output of DATEDIFF is an INT.

我之前遇到过这个问题(直接使用 DATEDIFF)并通过将 DATETIMEs 转换为 DECIMAL 来解决它，如下所示:

I've bumped into this before (using DATEDIFF directly) and resolved it by casting DATETIMEs to DECIMALs as follows:

DECLARE @d1 DATETIME
DECLARE @d2 DATETIME

DECLARE @n1 AS DECIMAL(38,20)
DECLARE @n2 AS DECIMAL(38,20)

SET @d1 = '2 Jan 2000 00:00:02'
SET @d2 = '1 Jan 2000 00:00:00'

-- @n1 and @n2 will hold the datetime in fractional form. The integer part
-- is the #days since 1 Jan 1900, whilst the fractional part is the time in
-- 1/86400's of a second (24 hours = 86400 seconds, so a fraction of 0.5
-- represents 12:00:00 noon precisely.
SELECT @n1 = CAST(@d1 AS DECIMAL(38,20)), @n2 = CAST(@d2 AS DECIMAL(38,20))

-- Now manipulate the fractional and integer parts of the time
-- to get the final seconds difference.
SELECT CAST(86400 AS DECIMAL(38,20)) * (@n1 - @n2)

SQL Server:datediff 函数导致溢出

相关推荐