{"id":4305,"date":"2015-12-12T00:57:34","date_gmt":"2015-12-11T19:27:34","guid":{"rendered":"http:\/\/sqlhints.com\/?p=4305"},"modified":"2015-12-23T07:07:29","modified_gmt":"2015-12-23T01:37:29","slug":"datediff_big-function-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2015\/12\/12\/datediff_big-function-in-sql-server-2016\/","title":{"rendered":"DATEDIFF_BIG Function in Sql Server 2016"},"content":{"rendered":"

DATEDIFF_BIG<\/strong> is one of the new function introduced in Sql Server 2016<\/strong>. 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.<\/p>\n

Syntax:<\/strong><\/p>\n

\r\nDATEDIFF_BIG ( datepart , startdate , enddate )\r\n<\/pre>\n
[ALSO READ] Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server<\/a><\/strong><\/p>\n
The request for this new function was submitted on the Microsoft connect site<\/a> some time back in 2008 by Erland Sommarskog.<\/p>\n
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\u2019s Min and Max value is: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.<\/p>\n
Example 1: Basic example<\/strong><\/p>\n
\r\nSELECT DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+1) 'DateDiff Big'\r\n<\/pre>\n
RESULT:<\/strong>
\n\"DATEDIFF_BIG<\/a><\/p>\n
ALSO READ:<\/strong> How to get difference between two dates in Years, Months and days in Sql Server<\/a><\/p>\n
Example 2:<\/strong> 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.<\/p>\n
\r\nSELECT DATEDIFF(ms, '2015-12-01', '2015-12-31') 'DateDiff'\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 535, Level 16, State 0, Line 1
\nThe 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.<\/p>\n
\r\nSELECT DATEDIFF_BIG(ms, '2015-12-01', '2015-12-31') 'DateDiff' \r\n<\/pre>\n
RESULT:<\/strong>
\n\"DATEDIFF_BIG<\/a><\/p>\n
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<\/p>\n
[ALSO READ]:<\/strong><\/p>\n