SQL Server: datediff function resulted in an overflow
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.
Sending a summary of SQL Server Agent job failures Unique constraint with EFCodeFirst and SqlCe4 SQL Server UNIQUE constraint with duplicate NULLs Basic data retrieval using $row SQL: Last_Value() returns wrong result (but First_Value() works fine) INSERT INTO SELECT gives: Column name or number of supplied values does not match table definition
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.
3 Solutions collect form web for “SQL Server: datediff function resulted in an overflow”
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.
I’ve bumped into this before (using DATEDIFF directly) and resolved by casting DATETIME’s 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)
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
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.