If you’ve ever needed to find the difference between two dates in SQL Server, you might have used the DATEDIFF() function. This function returns the amount of time between two dates using a datepart specified by you. For example, you could use it to return the number of days between date 1 and date 2. You can also get it to return the number of minutes, seconds, months, years, etc.

The DATEDIFF_BIG() function works exactly the same way, but with one subtle difference: Its return data type.

So the difference between these two functions is the data type of their return value.

• DATEDIFF() returns a signed integer (int)
• DATEDIFF_BIG() returns a signed big integer (bigint)

In some cases you won’t have much need to return a bigint data type. This represents a very big number, and whether you use DATEDIFF() or DATEDIFF_BIG() will make no difference (except for storage requirements – int uses 4 bytes, bigint uses 8 bytes).

However, 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.

In this case you’ll need to do one of three things:

• Use a shorter time period. For example, instead of trying to return the number of milliseconds in 26 days, try 25.
• Use a less precise datepart. For example, instead of trying to return the number of milliseconds, try it with just seconds.
• Use the DATEDIFF_BIG() function instead.

Sometimes the first two options aren’t suitable. If you need a time span of 26 days, 25 isn’t going to cut it. And if you need a Unix timestamp past 2038, you’ll encounter 2038 problems if you use DATEDIFF().

In any case, the DATEDIFF_BIG() function allows for a much larger range of results than DATEDIFF().

Example

Here are two examples to demonstrate both the similarities, and the differences between DATEDIFF() and DATEDIFF_BIG().

Same Result

Here’s an example where both functions produce the same result:

DECLARE @date1 datetime2 = SYSDATETIME();  
DECLARE @date2 datetime2 = DATEADD(second, 1, SYSDATETIME());
SELECT 
    DATEDIFF( millisecond, @date1, @date2 ) AS DATEDIFF,
    DATEDIFF_BIG( millisecond, @date1, @date2 ) AS DATEDIFF_BIG;

Result:

DATEDIFF  DATEDIFF_BIG
--------  ------------
1000      1000        

As expected, both functions return the same result. This is because the return value is small enough to cater for both bigint and int data types. All we did was return the number of milliseconds in 1 second.

Different Result

Now, here’s what happens if we increase the time frame to 1000 years.

First, here’s what happens when we use DATEDIFF():

DECLARE @date1 datetime2 = SYSDATETIME();
DECLARE @date2 datetime2 = DATEADD(year, 1000, SYSDATETIME());
SELECT DATEDIFF( millisecond, @date1, @date2 ) AS DATEDIFF;

Result:

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.

Now, here’s how DATEDIFF_BIG() handles it:

DECLARE @date1 datetime2 = SYSDATETIME();
DECLARE @date2 datetime2 = DATEADD(year, 1000, SYSDATETIME());
SELECT DATEDIFF_BIG( millisecond, @date1, @date2 ) AS DATEDIFF_BIG;

Result:

DATEDIFF_BIG  
--------------
31556908800000

Returning the number of milliseconds in 1000 years was too much for an int, but it’s not a problem for a bigint.