SQL Server: datediff function resulted in an overflow

Question

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.

Answer 1

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?

Answer 2

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.

Answer 3

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?

Answer 4

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)

Answer 5

Also check for triggers on the base tables. Sometimes quirky errors are returned because there is a bug in a trigger.

Answer 6

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.