SQL Server：datediff函数导致溢出

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

这个错误意味着什么，我该如何避免呢？

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。

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

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

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

What could I be doing wrong?

我能做错什么？

I'm using SQL Server 2008.

我正在使用SQL Server 2008。

4 个解决方案

#1

SQL Server maybe 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.

SQL Server可能在内部进行DATEDIFF进行比较，如果两个日期间隔超过68年（并且内部DATEDIFF是秒），DATEDIFF可能会出错，因为DATEDIFF的输出是INT。

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

我之前碰到过这个（直接使用DATEDIFF）并通过将DATETIME转换为DECIMAL来解决，如下所示：

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)

#2

I had the same issue because one of the records in my table had a default value for a datetime field of 1900-01-01 00:00:00.000.

我有同样的问题，因为我的表中的一个记录的日期时间字段为1900-01-01 00：00：00.000的默认值。

SELECT *
FROM Terminal 
WHERE DATEDIFF(SECOND, LastCheckIn, GETDATE()) < 30

DATEDIFF in the where clause will be evaluated for all the records in the table and will overflow on the LastCheckIn with value 1900-01-01 00:00:00.000

将为表中的所有记录评估where子句中的DATEDIFF，并在LastCheckIn上溢出值为1900-01-01 00：00：00.000

I solved it by first evaluating DATEDIFF for a difference in YEARS < 1

我通过首先评估DATEDIFF的年份<1的差异来解决它

This is the final query:

这是最终查询：

SELECT *
FROM Terminal 
WHERE
DATEDIFF(YEAR, LastCheckIn, GETDATE()) < 1
AND
DATEDIFF(SECOND, LastCheckIn, GETDATE()) < 30

#3

Thank you all for the pointers!

谢谢大家指点！

They made me recheck the vSomeView and it turns out that the vSomeView was doing a join between a view and some other tables. That view was doing a datediff to convert some datetime into a posix-style timestamp (seconds since epoch). Once I removed it, the query runs fine.

他们让我重新检查了vSomeView，结果发现vSomeView正在视图和其他一些表之间进行连接。该视图正在做一个约会，将一些日期时间转换为posix风格的时间戳（自纪元以来的秒数）。删除后，查询运行正常。

#4

SQL Server 2016 added DATEDIFF_BIG() which returns bigint.

SQL Server 2016添加了DATEDIFF_BIG（），它返回bigint。

#1