In SQL Server, you can use the DATEDIFF_BIG() function instead of the DATEDIFF() function if you expect the returned value to be really big. For example, if you’re trying to find out how many milliseconds are in a 1000 years, you’ll get an error.

That’s because DATEDIFF() returns an int data type, and the result is too big for that data type to handle. On the other hand, the DATEDIFF_BIG() function returns a signed bigint data type, which means you can use it to return much larger values. In other words, you can use with a much larger range of dates.

Other than that, there’s not really any difference between the two functions.

The article provides examples of using the DATEDIFF_BIG() function in SQL Server.

Syntax

First, here’s the syntax:

DATEDIFF_BIG ( datepart , startdate , enddate )

Where datepart is the part of the date that you want compared. startdate is the first date and enddate is the end date.

The function subtracts startdate from enddate.

The way it works is that it returns the count (as a signed big integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.

This is exactly the same syntax that’s used with the DATEDIFF() function.

Example 1

This is a basic example to demonstrate how it works.

SELECT DATEDIFF_BIG(day, '0001-01-01', '9002-01-01') AS Result;

Result:

+----------+
| Result   |
|----------|
| 3287547  |
+----------+

Note that in this case, we could’ve used DATEDIFF(), as the result is not too big for an integer.

Example 2

Here’s an example where we return the difference of various dateparts from two dates. In this case I declare two variables and assign two different dates to them (I use the DATEADD() function to add 1000 years to the first date):

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 1000, @date1);
SELECT 
    DATEDIFF_BIG( year, @date1, @date2 ) AS Years,
    DATEDIFF_BIG( quarter, @date1, @date2 ) AS Quarters,
    DATEDIFF_BIG( month, @date1, @date2 ) AS Months,
    DATEDIFF_BIG( week, @date1, @date2 ) AS Weeks,
    DATEDIFF_BIG( dayofyear, @date1, @date2 ) AS DayOfYear,
    DATEDIFF_BIG( day, @date1, @date2 ) AS Days;

Result:

+---------+------------+----------+---------+-------------+--------+
| Years   | Quarters   | Months   | Weeks   | DayOfYear   | Days   |
|---------+------------+----------+---------+-------------+--------|
| 1000    | 4000       | 12000    | 52178   | 365243      | 365243 |
+---------+------------+----------+---------+-------------+--------+

Again, we could’ve used DATEDIFF(), because none of the results are too big for an integer.

Example 3

In this example we return the hours, minutes, and seconds between two dates:

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 1000, @date1);
SELECT 
    DATEDIFF_BIG( hour, @date1, @date2 ) AS Hours,
    DATEDIFF_BIG( minute, @date1, @date2 ) AS Minutes,
    DATEDIFF_BIG( second, @date1, @date2 ) AS Seconds;

Result:

+---------+-----------+-------------+
| Hours   | Minutes   | Seconds     |
|---------+-----------+-------------|
| 8765832 | 525949920 | 31556995200 |
+---------+-----------+-------------+

Now we’re at the point where DATEDIFF() would’ve returned an error. The number of seconds is too big for an int (but not for a bigint).

Example 4

And finally, here’s an example with milliseconds, microseconds, and nanoseconds:

DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000';
DECLARE @date2 datetime2 = DATEADD(year, 100, @date1);
SELECT    
    DATEDIFF_BIG( millisecond, @date1, @date2 ) AS Milliseconds,
    DATEDIFF_BIG( microsecond, @date1, @date2 ) AS Microseconds,
    DATEDIFF_BIG( nanosecond, @date1, @date2 ) AS Nanoseconds;

Result:

+----------------+------------------+---------------------+
| Milliseconds   | Microseconds     | Nanoseconds         |
|----------------+------------------+---------------------|
| 3155760000000  | 3155760000000000 | 3155760000000000000 |
+----------------+------------------+---------------------+

In this case, we can clearly see the benefit that DATEDIFF_BIG() has over DATEDIFF(). DATEDIFF() would’ve fallen over at all three.