7

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.

  • what's happening in the view? – jimconstable Aug 13 '09 at 23:23
  • 2
    Does vSomeView use the DateDiff? – shahkalpesh Aug 13 '09 at 23:23
  • The view is doing doing a join between two tables and multiplying another column (not involved in this query) by a certain amount. The TimestampUTC column is not modified and DateDiff is not used. –  Aug 13 '09 at 23:34
  • 1
    Please post the view definition. Also, please check the objects referenced in the views, on the off-chance that they're also views or maybe even functions. – Philip Kelley Aug 14 '09 at 13:56
  • Are any of the base tables using computed columns? Are you able to interrogate the tables successfully without the view? Or the view without the where clause? – MartW Aug 13 '09 at 23:48
  • Also check for triggers on the base tables. Sometimes quirky errors are returned because there is a bug in a trigger. – HLGEM Aug 14 '09 at 14:53
  • I think timestamp is a reserved word.. but it looks like you are trying to use it as a column name? Either specify the table name first, and/or choose another column name. – RiddlerDev Aug 13 '09 at 23:27
  • No worries... are you sure the multplication is not inadvertantly causing the overflow? – RiddlerDev Aug 13 '09 at 23:43
  • Based on the name "vSomeView", it sounds like you are querying against a view. If so, what is the view definition? Perhaps it is using datepart? – Philip Kelley Aug 13 '09 at 23:23

5 Answers5

12

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)
Pang
  • 9,564
  • 146
  • 81
  • 122
Chris J
  • 30,688
  • 6
  • 69
  • 111
  • 1
    I don't think it works this way. If TimestampUTC is a datetime(which da says it is) or smalldatetime, SQL will convert '2009-08-13 22:17:00' into the same datatype, and then it's just a simple binary value comparison of the internal representations of a datetime. – Philip Kelley Aug 14 '09 at 13:55
  • You might be right. I've not got access to a 2008 box, but I do to 2005. Didn't have time to experiment at the time, but doing some poking now and I can't get the error described to occur. It would be useful to see the definition of the VIEW, however the method above may be able to resolve the issue if the failing DATEDIFF can be traced. – Chris J Aug 14 '09 at 15:27
4

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
Pilo
  • 1,210
  • 15
  • 11
  • 1
    I could't get this to work. I'm using sqlserver 2012. IIRC, SQL Server does not garantee order in the evaluation of where clause, and in my case, seems to always evaluate both conditions. – Jerther Apr 15 '16 at 13:36
  • Thank you for this answer! I had the same issue where a date in the table was the year 0017 instead of 2017... Updating the date fixed the overflow issue. – tnschmidt Jan 14 '19 at 19:18
2

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.

1

SQL Server 2016 added DATEDIFF_BIG() which returns bigint.

Taudris
  • 1,413
  • 1
  • 15
  • 23
0

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

mr R
  • 997
  • 2
  • 12
  • 25