DATEDIFF SQL Server Function


By:   |   Updated: 2020-07-16   |   Comments   |   Related: More > Dates

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:

DatePart Name Abbreviation
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns

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.

Example SQL Server DATEDIFF Code Output
How many minutes in a day? SELECT DATEDIFF(MINUTE, '07-04-2020', '07-05-2020') 1440 minutes
How many hours in a day? SELECT DATEDIFF(HOUR, '01-01-2020', '01-02-2020') 24 Hours
How many days in a year? SELECT DATEDIFF(DAY, '01-01-2020', '12-31-2020') 365 Days
How many months in a year? SELECT DATEDIFF(MONTH, '01-01-2019', '01-01-2020') 12 Months
How many years from 2000 to 2020? SELECT DATEDIFF(YEAR, '01-01-2000', '01-01-2020') 20 Years

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.

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

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.

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

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.

Msg 535, Level 16, State 0, Line 76
The datediff_big function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff_big with a less precise datepart.

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


Last Updated: 2020-07-16


get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager with Stericycle who has managed DBAs, BI Developer, and Data Management teams over the past 10 years.

View all my tips





Comments For This Article





download


Recommended Reading

Date and Time Conversions Using SQL Server

Format SQL Server Dates with FORMAT Function

Add and Subtract Dates using DATEADD in SQL Server

Creating a date dimension or calendar table in SQL Server

SQL Server Date and Time Functions with Examples





get free sql tips
agree to terms


Learn more about SQL Server tools