Categories: SQL server 2016

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.

 

DIFFERENCE BETWEEN TWO DATES BY DATEDIFF_BIG

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.

 

DIFFERENCE BETWEEN TWO DATES BY DATEDIFF_BIG

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.

 

DIFFERENCE BETWEEN TWO DATES BY DATEDIFF_BIG

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

 2,227 total views,  4 views today

admin

Share
Published by
admin

Recent Posts

What is slow changing dimension?

The attribute value in Dimension table are going to change slowly over time and they…

5 years ago

How to Change SQL Server Compatibility level

  Changing Database compatibility level will be required when you upgrade your database from one…

6 years ago

View Compatibility level of Database

To know what is compatibility Level check this post link. You can view the compatibility…

6 years ago

SQL Server Compatibility Level

SQL Server Compatibility level will be used to decide how SQL will use it features.…

6 years ago

View in SQL server

A view is a virtual table.  View will not store any data physically. The data…

6 years ago

SSIS :The package failed to load due to error 0xC0011008

Below is the error which I was getting today while deploying the SSIS pacakge for…

6 years ago