The DATEDIFF function returns a number of time units crossed between two specified dates. The function accepts the following three input arguments:
- datepart: This is the time unit (year, quarter, month, second, millisecond, microsecond, and nanosecond)
- startdate: This is an expression of any date data type (date, time, smalldatetime, datetime, datetime2, and datetimeoffset)
- enddate: This is also an expression of any date data type (date, time, smalldatetime, datetime, datetime2, and datetimeoffset)
The return type of the function is int. This means that the maximum returned value is 2,147,483,647. Therefore, if you specify minor units (milliseconds, microseconds, or nanoseconds) as the first parameters of the function, you can get an overflow exception for huge date ranges. For instance, this function call will still work, as follows:
SELECT DATEDIFF(SECOND,'19480101','20160101') AS diff;
It returns this result:
diff ----------- 2145916800
However, the following example will not work:
SELECT DATEDIFF(SECOND,'19470101','20160101') AS diff;
The result of this call is the following error message:
Msg 535, Level 16, State 0, Line 1 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.
Due to the mentioned data type limit, the maximal date difference that DATEDIFF can calculate when the second is used as date part parameter is about 68 years only. In the following table, you can find a list of date part units and the maximal-supported date differences for them:
Maximal supported date difference per date part for the function DATEDIFF
|
Date part |
Maximal supported date difference |
|
Hour |
250,000 years |
|
Minute |
4,086 years |
|
Second |
68 years |
|
Millisecond |
25 days |
|
Microsecond |
36 minutes |
|
Nanosecond |
2.15 seconds |
In order to cover a greater date range for short date parts, the SQL Server development team has added a new function in SQL Server 2016: DATEDIFF_BIG.
It has exactly the same interface as DATEDIFF; the only difference is its return type—bigint. This means that the maximal returned value is 9,223,372,036,854,775,807. With this function, you will not get any overflow even when you specify a huge date range and choose a minor date part. The following code calculates the difference between the minimal and maximal value supported by the datetime2 data type in MICROSECOND:
SELECT DATEDIFF_BIG(MICROSECOND,'010101','99991231 23:59:59.999999999') AS diff;
The following is the very large number representing this difference:
diff -------------------- 252423993599999999
However, even with the DATEDIFF_BIG function, you can get an exception if you call it for the same dates and choose the date part NANOSECOND:
SELECT DATEDIFF_BIG(NANOSECOND,'010101','99991231 23:59:59.999999999') AS diff;
The maximal value of the bigint data type is not enough to host this difference and to avoid an overflow:
Msg 535, Level 16, State 0, Line 1 The datediff_big function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff_big with a less precise datepart.
Of course, the last two statements are listed just for demonstration purposes; I cannot imagine a reasonable use case where you will need to represent 10,000 years in microseconds or nanoseconds. Therefore, you can say that DATEDIFF_BIG meets all the reasonable requirements related to date difference calculations.