SQL SERVER – Learning DATEDIFF_BIG Function in SQL Server 2016

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)