DATEDIFF_BIG – SQL Server 2016 new fn

Hello Friends!

In continuation to my previous blog about SQL Server 2016 newly introduced T-SQL Functions, putting down DATEDIFF_BIG another the new function. This function helps to find difference between two dates in the units specified by the DatePart parameter and the returned unit is of type bigint. This function like DATEDIFF function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate. The difference between these two functions is the return type. DATEDIFF functions return type is INT, whereas the DATEDIFF_BIG functions return type is BIGINT.



DATEDIFF_BIG ( datepart , startdate , enddate )

BIGINT range is from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Think about DATEDIFF, any difference in Micro & Nano second is out of the the above range, DATEDIFF returns a value or an error.

With the help of extended range, you can now calculate date different up to milliseconds & nano seconds, however that makes me really worried. Why someone or an application would seek difference in milliseconds or nano seconds until rock science comes into picture.


About Avanish Panchal

Avanish carries around 15 years of experience in IT industry. He is post graduate in Computer Science, followed by Masters in Business Administration. He has worked on multiple technologies like SQL Server, .net & Sybase with world largest bank(s)/IT consulting firm(s) like JPMorganChase, CapGemini, Datamatics etc. Currently holds position of Database Architect in BioPharma Global Leader. His area of focus are SQL Server DB Engine, Security, Storage, Virtualization & Infrastructure Consolidation. Passionate for delivering Database IT Infrastructure to satisfy and exceed the needs of the enterprise. You may find him active on various forums like SQLBangalore, SQLServerGeeks & many more. His SQL Server passion helped him to be the Core Team member of Asia's First SQL Server Conference in 2015 @ Bangalore (#SSGAS2015).

View all posts by Avanish Panchal →

Leave a Reply

Your email address will not be published.