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
No comments:
Post a Comment