SQL Server: datediff function resulted in an overflow

bpsygsoo 于 2023-03-07 发布在 SQL Server

关注(0)|答案(5)|浏览(159)

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.

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'

What could I be doing wrong?

I'm using SQL Server 2008.

sql-server

来源：https://stackoverflow.com/questions/1275208/sql-server-datediff-function-resulted-in-an-overflow

5条答案

按热度按时间

bbmckpt71#

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.

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)

赞(0）回复(0）举报 2023-03-07

2lpgd9682#

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.

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

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

This is the final query:

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

赞(0）回复(0）举报 2023-03-07

u5rb5r593#

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.

赞(0）回复(0）举报 2023-03-07

vom3gejh4#

SQL Server 2016 added DATEDIFF_BIG() which returns bigint .

赞(0）回复(0）举报 2023-03-07

kyvafyod5#

I created function which should bypass this limitation on SQL versions lower than SQL2016 see here .

It should work similliar to DATETIMEDIFF_BIG in simple scenarios

赞(0）回复(0）举报 2023-03-07