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:
Thank you for the reading. Please share your input.
