This article is half-done without your Comment! *** Please share your thoughts via Comment ***
SQL Server 2016 introduced a new function for data operation which is DATEDIFF_BIG().
Previously, we were unable to take date difference in format of MICROSECOND, NANOSECOND, MILLISECOND using DATADIFF().
In our new module, we need to make time difference in a microsecond as we are dealing with financial transactions.
If you are using DATEDIFF() and try to get date difference in format of MICROSECOND, NANOSECOND, MILLISECOND, you will get a below error.
Because the return value from the DATEDIFF function is an integer with a range of -2^31 to 2^31 – 1.
|
1 2 3 |
SELECT DATEDIFF(MICROSECOND,'2015-08-26 09:12:56.147488','2016-10-30 08:09:14.965238') SELECT DATEDIFF(NANOSECOND,'2015-08-26 09:12:56.147488','2016-10-30 08:09:14.965238') SELECT DATEDIFF(MILLISECOND,'2015-08-26 09:12:56.147488','2016-10-30 08:09:14.965238') |
|
1 2 |
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. |
In SQL Server 2016, we can use DATEDIFF_BIG() to take date difference in format of MICROSECOND, NANOSECOND, MILLISECOND.
|
1 2 3 |
SELECT DATEDIFF_BIG(MICROSECOND,'2015-08-26 09:12:56.147488','2016-10-30 08:09:14.965238') SELECT DATEDIFF_BIG(NANOSECOND,'2015-08-26 09:12:56.147488','2016-10-30 08:09:14.965238') SELECT DATEDIFF_BIG(MILLISECOND,'2015-08-26 09:12:56.147488','2016-10-30 08:09:14.965238') |
Leave a Reply