DATEDIFF SQL Server Function

Problem

Often, we need to calculate the difference between two dates and return the results in a desired date part or increment such as days, hours, minutes. Fortunately, SQL Server provides a function for this.

In this article I will demo the SQL functions DATEDIFF and DATEDIFF_BIG and share several examples of how to use each. I will also show you the limitations and how to work around them. You will also learn how to calculate how old the city of St. Augustine is in nanoseconds.

Solution

We will explore the DATEDIFF and DATEDIFF_BIG functions, show how they are used, and provide several examples.

What is SQL Server DATEDIFF Function

DATEDIFF() is a basic SQL Server function that can be used to do date math.  Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months days, minutes, seconds as an int (integer) value.

Syntax:

DATEDIFF( DatePart, StartDate, EndDate )

What is SQL Server DATEDIFF_BIG Function

DATEDIFF_BIG() is a SQL function that was introduced in SQL Server 2016. It can be used to do date math as well.  Specifically, it gets the difference between 2 dates with the results returned in date units specified as years, months days, minutes, seconds as a bigint value.

Syntax:

DATEDIFF_BIG( DatePart, StartDate, EndDate )

How to use DATEDIFF and DATEDIFF_BIG

The usage for DATEDIFF and DATEDIFF_BIG are the same: DATEDIFF(datepart of return values, Start Date, End Date).

Examples:

SELECT DATEDIFF( MILLISECOND, '07-04-2020', '07-05-2020') --> = 86400000 
SELECT DATEDIFF_BIG( NANOSECOND, '07-04-2020', '07-05-2020') --> = 86400000000000 

 First Parameter: is a valid datepart argument which is one of the following from nanosecond to year:

Second Parameter: is the Start Date.  A valid date, datetime, datetimeoffset, datetime2, smalldatetime, or time data type variable or a string that resolved to a datetime datatype.

Third Parameter: is the End Date. A valid date, datetime, datetimeoffset, datetime2, smalldatetime, or time data type variable or a string that resolved to a datetime datatype.

*This information can be found on Microsoft Docs!

SQL Server DATEDIFF Examples

Below are basic examples using the most common datapart arguments.

When to use SQL Server DATEDIFF_BIG function

Use the DATEDIFF_BIG function when your results exceed the range of an integer value which is between (-2,147,483,648 to +2,147,483,647).

BIGINT has a range of (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807). It seems that it would be hard to exceed this range! But we will present an example that does exceed this range when using DATEDIFF_BIG.

SQL Server DATEDIFF_BIG instead of DATEDIFF

How to exceed the DATEDIFF int return value? Microsoft Docs provides 2 examples of how to overflow the DATEDIFF integer return value which I demonstrate below and show how DATEDIFF_BIG works to get around the limitation.

--1. For millisecond, max difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. 
--   the following will exceed this range.
SELECT DATEDIFF(MILLISECOND, '01-01-2020', '02-01-2020') 

This results in the following error.

Here is another example.

--2. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. 
--   the following will exceed this range.
SELECT DATEDIFF(SECOND, '01-01-1950', '02-01-2020') 

This results in the following error.

Using DATEDIFF_BIG instead to avoid the above errors.

--3. How to get around the integer limit using DATEDIFF_BIG function
SELECT DATEDIFF_BIG(MILLISECOND, '01-01-2020', '02-01-2020') --> = 2678400000
SELECT DATEDIFF_BIG(SECOND, '01-01-1950', '02-01-2020') –-> = 2211667200 

When DATEDIFF_BIG is not Big Enough

According to Microsoft Docs, DATEDIFF_BIG can only overflow if using nanosecond precision where the difference between enddate and startdate is more than 292 years, 3 months, 10 days, 23 hours, 47 minutes and 16.8547758 seconds.

This could be an issue if you want to know how many nanoseconds it has been since Americas oldest city, St. Augustine Florida was founded!

--1. How many nanoseconds since St. Augustine was founded on Sept. 8, 1565?
SELECT DATEDIFF_BIG(NANOSECOND, '09-08-1565', GETDATE())

Bummer!! We get an error.

Let's try this in multiple steps:

--1. First get the number of Years since St. Augustine was founded
SELECT DATEDIFF_BIG(YEAR, '09-08-1565', GETDATE()) --> = 455
 
--2. Next, get the number of nanoseconds in a year
SELECT DATEDIFF_BIG(NANOSECOND, '01-01-2020 00:00:00.0000000', '12-31-2020 23:59:59.9999999') --> = 31622399999999900
--3. Last multiply the results together and cast to decimal(38,0)
-- 455 x 31622399999999900 = 14388191999999954500
SELECT CAST(455 * 31622399999999900 as DECIMAL(38,0)) --> = 14388191999999954500

Walla! Now we know how many nanoseconds since the founding of St. Augustine.

Wrap Up

I hope you enjoyed this exercise on DATEDIFF and DATEDIFF_BIG. DATEDIFF is a commonly used SQL function that has been around for many years. DATEDIFF_BIG is a relatively new function that was introduced in SQL Server 2016. Though I provided examples of overflow from these functions it is rare that you would encounter these scenarios. These are handy functions that should be added to your SQL toolbox!

Next Steps

• Go more in depth with – other Datediff articles
• Read about – work arounds for calculating age
• Read about other interesting SQL Functions – LAG and LEAD
• Find other articles on Datetime – MS SQL Tips Search on DateDiff