Tag Archives: DATEDIFF_BIG in Sql 2016

Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

Sql Server, Sql Server 2016, , , , , , , , , , ,

This article gives an introduction to the DATEDIFF and DATEDIFF_BIG functions by listing out the differences and similarities between them.

[ALSO READ]: How to get difference between two dates in Years, Months and days in Sql Server

DATEDIFF Function DATEDIFF_BIG Function
This function is available from very old versions of Sql Server. This function is introduced in Sql Server 2016
This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate This function returns the number of the specified datepart boundaries crossed between the specified startdate and enddate
Syntax:
DATEDIFF( datepart, startdate, enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

Syntax:
DATEDIFF_BIG(datepart,startdate,enddate)

Where datepart can be one of the following values: year, quarter, month, dayofyear, day, week, hour, minute, second, millisecond, microsecond and
nanosecond

This functions return value data type is INT This functions return value data type is BigINT
Example: Below is an example getting difference between two dates in days using DATEDIFF function:

SELECT DATEDIFF(DAY,
 '12/01/2015','12/02/2015')
 AS 'Difference in days'

RESULT:
Difference in days
——————
1

Example: Below is an example getting difference between two dates in days using DATEDIFF_BIG function:

SELECT DATEDIFF_BIG(DAY,
 '12/01/2015','12/02/2015')
  AS 'Difference in days'

RESULT:
Difference in days
——————–
1

Example: This example demonstrates the behavior of the DATEDIFF function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(MILLISECOND,
 '12/01/2015','12/30/2015')
 AS 'Difference in MILLISECOND'

RESULT:

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.

 Example: This example demonstrates the behavior of the DATEDIFF_BIG function when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF_BIG(MILLISECOND,
 '12/01/2015','12/30/2015') 
AS 'Difference in MILLISECOND'

RESULT:
Difference in MILLISECOND
————————-
2505600000

The minimum and maximum value that this function can return is: -2,147,483,648 and +2,147,483,647 The minimum and maximum value that this function can return is: -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

ALSO READ

DATEDIFF_BIG Function in Sql Server 2016

Sql Server, Sql Server 2016, , , , , , , , , ,

DATEDIFF_BIG is one of the new function introduced in Sql Server 2016. It gives the difference between the 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.

Syntax:

DATEDIFF_BIG ( datepart , startdate , enddate )

[ALSO READ] Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server

The request for this new function was submitted on the Microsoft connect site some time back in 2008 by Erland Sommarskog.

With DATEDIFF function for milliseconds the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years. This is because the return type of the DATEDIFF function is INT and INT datatypes Min and Max value is: -2,147,483,648 to +2,147,483,647. But with DATEDIFF_BIG function the maximum difference is very high as the return type is bigint and it’s Min and Max value is: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.

Example 1: Basic example

SELECT DATEDIFF_BIG(DAY, GETDATE(), GETDATE()+1) 'DateDiff Big'

RESULT:
DATEDIFF_BIG Basic example

ALSO READ: How to get difference between two dates in Years, Months and days in Sql Server

Example 2: Below example demonstrates how DATEDIFF and DATEDIF_BIG functions behave differently when the milliseconds difference between two dates is greater than the INT max (i.e. 2,147,483,647) value.

SELECT DATEDIFF(ms, '2015-12-01', '2015-12-31') 'DateDiff'

RESULT:

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.

SELECT DATEDIFF_BIG(ms, '2015-12-01', '2015-12-31') 'DateDiff'

RESULT:
DATEDIFF_BIG Sql Example

So, if we know that sometime the difference between two dates is going to cross the INT max value then we have to use the DATEDIFF_BIG function

[ALSO READ]: