Dates and Times in SQL Server: DATEDIFF() and DATEDIFF_BIG()

Last time we looked at adding or subtracting date parts using the DATEADD() T-SQL system function. This week we see how to calculate the difference between two date-time values using and DATEDIFF_BIG().

The syntax for both functions is identical:

DATEDIFF (datepart, startdate, enddate)
-- returns a result as an INT

DATEDIFF_BIG (datepart, startdate, enddate)
-- returns a result as a BIGINT

The only functional difference between them is that the DATEDIFF_BIG() returns values as a , for results that exceed the boundary of an INT. Keep this in mind when deciding which one to use. For example, the maximum number of seconds an INT can hold is 68 years, while a BIGINT can comfortably store the number of seconds in 10,000 years. This becomes especially important when dealing with microseconds and nanoseconds.

The rest of the post will use DATEDIFF() to refer to both functions.

The following table may look familiar to regular readers of this site. Here are the datepart options we can use in DATEDIFF(). While abbreviations are supported, it is better to use the full expression to ensure clarity in our code.

Calculate the Difference in Seconds Between Two Dates

One of the most common scenarios is to calculate the number of seconds (or milliseconds or even nanoseconds) between two events. The code might look something like this (with demo data in the startdate and enddate parameters):

DECLARE @dt1 AS DATETIME2(7) = CAST('2018-11-14 09:05:12.2390114' AS DATETIME2(7));
DECLARE @dt2 AS DATETIME2(7) = CAST('2018-11-14 09:02:03.8440911' AS DATETIME2(7));
SELECT DATEDIFF(SECOND, @dt1, @dt2);

In this contrived example, the result is an integer with the value of -189, or 189 seconds. Note that if the input values were reversed, the result would be a positive number. In most scenarios, we would convert this to an absolute value to avoid negative amounts, by using the T-SQL system function.

Date Boundaries

The official documentation makes specific reference to datepart boundaries when using DATEDIFF(). In other words, we need to be aware of the behaviour when crossing these boundaries.

In the example provided (reproduced below), despite the start and end dates differing by only 100 nanoseconds, they all return a value of 1 because they cross a datepart boundary, moving from one year into the next (or quarter, month, day, hour, etc.).

SELECT DATEDIFF_BIG(year,        '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(quarter,     '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(month,       '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(dayofyear,   '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(day,         '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(week,        '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(hour,        '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(minute,      '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(second,      '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');
SELECT DATEDIFF_BIG(millisecond, '2018-12-31 23:59:59.9999999', '2019-01-01 00:00:00.0000000');

Other Considerations

Owing to the granularity of certain data types, rounding errors may occur. For example, the SMALLDATETIME data type is only accurate to the nearest minute, so any calculation using these as input parameters and expecting a number of seconds will round up or down to the nearest minute as well.

Another consideration is where no date or time is used, respectively. In other words, we are calculating the difference between two times. In such cases, the date 1900-01-01 will be prefixed to the time. For situations where no time is included in the input dates, a time of 00:00:00 will be substituted.

Share your use cases for DATEDIFF() and DATEDIFF_BIG() in the comments below.