DATEDIFF_BIG is one of the new function introduced in Sql Server 2016. It gives the difference between the two dates in the units specified by the DatePart parameter and the returned unit is of type bigint. This function like DATEDIFF function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate. The difference between these two functions is the return type. DATEDIFF functions return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.
Syntax:
DATEDIFF_BIG ( datepart , startdate , enddate )
[ALSO READ] Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
The request for this new function was submitted on the Microsoft connect site some time back in 2008 by Erland Sommarskog.
With DATEDIFF function for milliseconds the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years. This is because the return type of the DATEDIFF function is INT and INT datatypes Min and Max value is: -2,147,483,648 to +2,147,483,647. But with DATEDIFF_BIG function the maximum difference is very high as the return type is bigint and it’s Min and Max value is: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
Example 1: Basic example
SELECT DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+1) 'DateDiff Big'
RESULT:
ALSO READ: How to get difference between two dates in Years, Months and days in Sql Server
Example 2: Below example demonstrates how DATEDIFF and DATEDIF_BIG functions behave differently when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.
SELECT DATEDIFF(ms, '2015-12-01', '2015-12-31') 'DateDiff'
RESULT:
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.
SELECT DATEDIFF_BIG(ms, '2015-12-01', '2015-12-31') 'DateDiff'
RESULT:
So, if we know that sometime the difference between two dates is going to cross the INT max value then we have to use the DATEDIFF_BIG function
[ALSO READ]:
- Native JSON Support in Sql Server 2016
- DROP IF EXISTS Statement in Sql Server 2016
- Compare Execution Plans in Sql Server 2016
- Live Query Statistics in Sql Server 2016
- DATEDIFF_BIG Function in Sql Server 2016
- Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
- SESSION_CONTEXT in Sql Server 2016