How DATEDIFF_BIG a new feature of SQL SERVER 2016? DATEDIFF_BIG Function in Sql Server 2016

DATEDIFF_BIG Function in Sql Server 2016

DATEDIFF_BIG is one of the new function introduced in Sql Server 2016. 

It returns the difference of between the specified start date and end date of specified datepart boundaries. This function is like DATEDIFF function. DATEDIFF functions return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.

Syntax:

DATEDIFF_BIG (datepart, startdate , enddate )

Example 1: Basic example

SELECT DATEDIFF_BIG (DAY, GETDATE(), GETDATE()+3) 'DateDiff'

DATEDIFF_BIG Function in Sql Server 2016

Example 1: DATEDIFF VS DATEDIF_BIG functions 

SELECT DATEDIFF(MILLISECOND, '01/01/2017','12/31/2017') 'Diff in MILLISECOND'

Above statement will give the error because the return type of DATEDIFF is INT and the returning value is greater than INT. the error is below.

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.

To avoid this error Sql Server 2016 provided DATEDIF_BIG functions below is the query

SELECT DATEDIFF_BIG(MILLISECOND, '01/01/2017','12/31/2017')  'Diff in MILLISECOND'

Result:-

Diff in MILLISECOND

31449600000