DATEDIFF and DATEDIFF_BIG – SQL Server 2016


We are already familiar with DATEDIFF function introduced in the very initial version of SQL Server. But, in SQL Server 2016, Microsoft has introduced DATEDIFF_BIG function which can be used to compute the difference between two given dates in terms of the given date part. In this post, we are going to explore the use of the DATEDIFF_BIG function.

Lets have a look on the syntax of both these functions:

Syntax:

DATEDIFF

DATEDIFF(<datepart>, startdate, enddate)

DATEDIFF_BIG

DATEDIFF_BIG(<datepart>, startdate, enddate)

Difference between DATEDIFF and DATEDIFF_BIG function

In above syntax, we can see that both the functions have similar syntax and any of them can be used to get the difference between two dates. However, these functions have different return types. DATEDIFF function returns an integer value as a difference between two dates, whereas DATEDIFF_BIG function returns a big integer value as a difference.

The range of the integer value in SQL Server is from -2,147,483,648 to +2,147,483,647. In case we need to return a value beyond this range, we cannot use DATEDIFF function. Returning a value beyond the range of int using DATEDIFF function, will raise an error as below:

DECLARE @StartDate DATETIME = '20160714', @EndDate DATETIME = '20160814'
SELECT DATEDIFF(MILLISECOND, @StartDate, @EndDate) AS DiffInMilliSecond
GO

Output:

Msg 535, Level 16, State 0, Line 2
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.

If the returning date part is large enough to fit inside the range of integer, we are good to use DATEDIFF function. But, when the return value crosses the range of the integer data type, DATEDIFF function throws an error as above.

To overcome this limit, SQL Server 2016 has introduced DATEDIFF_BIG function which can be used in scenarios where date part is too small and prone to cross the range of the integer data type. As DATEDIFF_BIG function returns a big int data type ranges between -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807, we get a wider window than integer data type.

DECLARE @StartDate DATETIME = ‘20160714’, @EndDate DATETIME = ‘20160814’
SELECT DATEDIFF_BIG(MILLISECOND, @StartDate, @EndDate) AS DiffInMilliSecond
GO

Output:

DATEDIFF_BIG - Difference in millisecond

DATEDIFF_BIG – Difference in millisecond

Thank you for the reading. Please share your input.

Rate This
[Total: 4    Average: 3.5/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

I am Gopal Krishna Ranjan, having 6+ years of industry experience in Software development using Microsoft technologies. I have a head down experience in Database development, performance tuning in SQL Server, T-SQL optimization, BI (Business Intelligence) project implementation, reporting in SSRS, using SSIS for ETL, implementing multi dimensional and tabular data-warehouse in SSAS, querying cubes using MDX and DAX, Windows and Web Applications development with C#.

Leave a comment

Your email address will not be published. Required fields are marked *

*