This article provides a solution to a problem you may occasionally encounter while using the DATEDIFF() function in SQL Server.

If you encounter the following 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.

It’s because the return value is too large. The DATEDIFF() function returns its result as an int data type. The reason you got this message is that the return value is too big for the int data type. Fortunately there’s an easy way to fix this.

The quickest and easiest way to fix this issue is to switch to the DATEDIFF_BIG() function. This function works exactly like DATEDIFF(), except that its return data type is a signed bigint. In other words, it can handle really big numbers.

Example

Let’s see if DATEDIFF() can tell us how many milliseconds are in a thousand years:

SELECT DATEDIFF( 
    millisecond, 
    SYSDATETIME(), 
    DATEADD(year, 1000, SYSDATETIME()) ) AS 'Milliseconds in 1000 years';

Result:

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. 

Maybe not. Seems there are too many milliseconds to fit into an int.

Time for the big guns. DATEDIFF_BIG(), do your stuff…

SELECT DATEDIFF_BIG( 
    millisecond, 
    SYSDATETIME(), 
    DATEADD(year, 1000, SYSDATETIME()) ) AS 'Milliseconds in 1000 years';  

Result:

Milliseconds in 1000 years
--------------------------
31556908800000            

That’s better.

If you still get the above error message, you must be trying to return a really big number. In that case you’ll need to do at least one of the following:

• Use a shorter time period.
• Use a less precise datepart. For example, instead of trying to return the number of milliseconds, try it with just seconds.

Also note that you’ll need to be running at least SQL Server 2016 before you can use the DATEDIFF_BIG() function.