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

SQL SERVER - Learning DATEDIFF_BIG Function in SQL Server 2016 DATEDIFF_BIG-800x164

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)

, , ,
Previous Post
SQL Server Management Studio – New Feature – Object Explorer Query Tracking
Next Post
SQL SERVER – How to Migrate Master Database to New Location?

Related Posts

2 Comments. Leave new

  • PradeeP KumaR GuptA
    August 23, 2017 10:39 am

    A Lot problems solved by this Article. Thanks Sir

    Reply
  • Bernhard “Bard” Döbler
    August 27, 2017 3:55 am

    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.

    Reply

Leave a Reply

Menu