DIFFERENCE BETWEEN TWO DATES BY DATEDIFF_BIG

DIFFERENCE BETWEEN TWO DATES BY DATEDIFF_BIG:

DATEDIFF_BIG is a new function introduced in SQL Server 2016. We will also see what is the difference between the DATEDIFF and the new DATEDIFF_BIG function.

The syntax for DATEDIFF and DATEDIFF_BIG are both similar however DATEDIFF will return a int value and the DATEDIFF_BIG will return a big int value.

DATEDIFF : Syntax DATEDIFF ( datepart , startdate , enddate )

Return type is int .

If the return value not in the range for int (-2,147,483,648 to +2,147,483,647), an error is returned.

Datediff was available starting from SQL server version 2008.

Below is an example of how it is used .

select DATEDIFF( day,’01-Feb-18′,’12-Feb-2018′) as Days ,

datediff( MILLISECOND,’01-Feb-18′,’12-Feb-2018′) as Milliseconds.

This DATEDIFF function works good till Milliseconds however if you go further range after that it will throw the below error.

select DATEDIFF( day,’01-Feb-18′,’12-Feb-2018′) as Days ,

DATEDIFF( MILLISECOND,’01-Feb-18′,’12-Feb-2018′) as Milliseconds,

DATEDIFF(MICROSECOND,’01-Feb-18′,’12-Feb-2018′) as Microsecond

Msg 535, Level 16, State 0, Line 15

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 lets see how this will work with the new Datediff_big function.

DATEDIFF_BIG : Syntax DATEDIFF_BIG ( datepart , startdate , enddate )

Return type is bigint.

If the return value is not in range for big int  (

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807), an error is returned.

This DATEDIFF_BIG function is available from SQL Server version 2016.

Below is an example of DATEDIFF_BIG.

select DATEDIFF_BIG( day,’01-Feb-18′,’12-Feb-2018′) as Days ,

DATEDIFF_BIG( MILLISECOND,’01-Feb-18′,’12-Feb-2018′) as Milliseconds,

DATEDIFF_BIG(MICROSECOND,’01-Feb-18′,’12-Feb-2018′) as Microsecond.

The DATEDIFF_BIG do not throw error for the Microsecond which we have seen earlier in the DATEDIFF.

 2,194 total views,  3 views today