One of the T-SQL functions introduced in SQL Server 2016 is DATEDIFF_BIG function.
In older versions there is a limit on the return value of DATEDIFF function. The return datatype of INT and the maximum it can have the value 2147483647. It will not return any value more than this number.
Consider the following example
SELECT DATEDIFF(SECOND, '19000101', '20000101') AS SECONDS_SINCE_1900
The above code should return the number of seconds since Jan 01, 1900 to Jan 01,2000. But it returns the following error
Msg 535, Level 16, State 0, Line 3
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 you can use the DATEDIFF_BIG function to get the correct result
SELECT DATEDIFF_BIG(SECOND, '19000101', '20000101') AS SECONDS_SINCE_1900
The result is
This function is very similar to handling very big numbers like COUNT_BIG function which was introduced in version 2012. To be honest, I have yet not seen people using this function much in the production as this is a pretty new function and not everyone needs this particular function.
Let me know if you are using this function in the business, I would love to know how you are using this particular feature in the business. Please leave a note.
Reference: Pinal Dave (https://blog.sqlauthority.com)
2 Comments. Leave new
A Lot problems solved by this Article. Thanks Sir
A customer requests I pass him dates as seconds in the UNIX epoch. I’m currently limited to SQL 2012 but would love to use DATEDIFF_BIG for that calculation as it allows to work with dates in the year 2038 and later.