SQL 2016 – DATEDIFF_BIG

I don’t know about most guys out there but I personally feel this was a great addition to SQL 2016. I use the datetime2 function quite a bit when analyzing performance issues. This trend has started only recently in my case as we started using in memory OLTP tables and Columstore indexes etc. and were getting sub millisecond performance. It became difficult to monitor performance improvements since the datedfiff function wouldn’t scale beyond the int datatype.

An example of this is shown below

declare @date datetime2 = getdate()
waitfor delay '00:00:20'

select DATEDIFF(ns,@date, getdate())


The above query would return the error

Msg 535, Level 16, State 0, Line 6

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.

While the below query return the correct datediff value in NS


declare @date datetime2 = getdate()
waitfor delay '00:00:20'

select DATEDIFF_BIG(ns,@date, getdate())

Output