CODEWITHSUNDEEP
sqlsql-server
Compy
Compy

Reputation: 1177

SQL Server DATEDIFF results in overflow on day comparison

Got a slight issue, I've got a view with several hundred thousand rows (and only going to get exponentially bigger) with a column with a datetime like so: 2017-07-10 12:13:46.000.

I'm trying to only select items with a timestamp in the last 7 days. I've got this:

SELECT Top(100) * FROM vw_dataList
WHERE DATEDIFF( DAY, vw_dataList.startTime, CURRENT_TIMESTAMP ) < 7;

But this results in an error:

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'm not really sure why this is, as even if DATEDIFF creates an integer from the timestamp, it shouldn't be such a big integer as to cause an overflow should it? Not really sure where to go from here so any advice is appreciated!

Cheers!

Upvotes: 0

Views: 515

Answers (2)

Rog&#233;rio Carvalho Batista
Rog&#233;rio Carvalho Batista

Reputation: 462

Seems that the plan for this query will be better if you use:

WHERE w_dataList.startTime > GETDATE() - 7

As you commented that the size of your table is too big, you can also insert a index in this column that probably won't be used with DATEDIFF() function.

Index sample:

CREATE INDEX ix_dataList ON vw_dataList (startTime DESC);

PS: Seems that it is a view, so you should replace the view to insert it in your table.

PS2: Check if you really need this index, you can check it in the execution plan.

Upvotes: 0

DavidG
DavidG

Reputation: 118977

It looks like you have a date in your table that is significantly far in the past or future that is causing the DATEDIFF function to overflow. That function returns a signed integer so any date that is 2 billion (give or take) days in the future or past will overflow.

One option is to not use DATEDIFF at all and instead use DATEADD subtract 7 days from the current time and use that to compare:

SELECT TOP(100) * 
FROM vw_dataList
WHERE vw_dataList.startTime >= DATEADD(DAY, -7, CURRENT_TIMESTAMP)

A possible alternative, though I wouldn't recomment it in this situation is to use DATEDIFF_BIG as this returns a BIGINT.

Upvotes: 1

Related Questions