Sometimes you need to use T-SQL to compute the time passed between two events whose date and time values you get as inputs. For some purposes, it’s sufficient to compute the difference as a duration in terms of some part. What’s significantly trickier is to compute the difference as a combination of parts.

Sometimes you need to use T-SQL to compute the time passed between two events whose date and time values you get as inputs. For some purposes, it’s sufficient to compute the difference as a duration in terms of some part, such as seconds, using the DATEDIFF or DATEDIFF_BIG function. The former returns the difference in the desired part as a four-byte integer (INT); the latter (added in SQL Server 2016) returns the difference as an 8-byte integer (BIGINT). There’s a bit of trickiness there, especially prior to SQL Server 2016, when the difference doesn’t fit in the returned type, but it’s not that hard to solve. What’s significantly trickier is to compute the difference as a combination of parts, starting with years and going down to nanoseconds. For example, given the two input date and time values @dt1 = '19710212 12:00:00.0000001' and @dt2 = '20170208 12:00:00.0000000', you want the computation to tell you that the difference is 45 years, 11 months, 26 days, 23 hours, 59 minutes, 59 seconds and 999,999,900 nanoseconds, with a positive sign (since @dt1 < @dt2).

In this article, I provide a solution in the form of an inline table-valued function (TVF) called DATEDIFFPARTS. It accepts two DATETIME2 inputs called @dt1 and @dt2 and returns a result set with one row, with one column for the sign of the result (1 when @dt1 < @dt2, -1 when @dt1 > @ft2, 0 when they are the same, and NULL if any is NULL) and one column for each of the parts.

It’s quite an interesting and fun puzzle, so I suggest you try solving it by yourself before looking at my solution. Use the following code to test it with two constant inputs:

SELECT sgn, yy, mm, dd, hh, mi, ss, ns

FROM dbo.DATEDIFFPARTS('19710212 12:00:00.0000001', '20170208 12:00:00.0000000');

This code is supposed to generate the following output:

sgn yy mm dd hh mi ss ns

---- --- --- --- --- --- --- ----------

1 45 11 26 23 59 59 999999900

As an inline TVF you can apply it to some table that holds interval delimiters to compute the difference for each interval, like so:

SELECT dt1, dt2, sgn, yy, mm, dd, hh, mi, ss, ns

FROM ( VALUES('19710212 12:00:00.0000001', '20170208 12:00:00.0000000'),

('19710212 12:00:00.0000001', '19710212 12:00:00.0000001'),

('20170208 12:00:00.0000000', '19710212 12:00:00.0000001'),

('19710212 12:00:00.0000001', NULL) )

AS D(dt1, dt2)

CROSS APPLY dbo.DATEDIFFPARTS(dt1, dt2) AS F;

This code is supposed to generate the following output (formatted as two parts for clarity):

keycol dt1 dt2

------- ------------------------- -------------------------

1 19710212 12:00:00.0000001 20170208 12:00:00.0000000

2 19710212 12:00:00.0000001 19710212 12:00:00.0000001

3 20170208 12:00:00.0000000 19710212 12:00:00.0000001

4 19710212 12:00:00.0000001 NULL

keycol sgn yy mm dd hh mi ss ns

------- ---- ---- ---- ---- ---- ---- ---- ----------

1 1 45 11 26 23 59 59 999999900

2 0 0 0 0 0 0 0 0

3 -1 45 11 26 23 59 59 999999900

4 NULL NULL NULL NULL NULL NULL NULL NULL

Good luck!

**Solution**

Here’s the definition of the DATEDIFFPARTS function that I came up with:

IF OBJECT_ID(N'dbo.DATEDIFFPARTS', N'IF') IS NOT NULL

DROP FUNCTION dbo.DATEDIFFPARTS;

GO

CREATE FUNCTION dbo.DATEDIFFPARTS(@dt1 AS DATETIME2, @dt2 AS DATETIME2)

RETURNS TABLE

AS

RETURN

SELECT

sgn,

yydiff - subyy AS yy,

(mmdiff - submm) % 12 AS mm,

DATEDIFF(day, DATEADD(mm, mmdiff - submm, dt1), dt2) - subdd AS dd,

nsdiff / CAST(3600000000000 AS BIGINT) AS hh,

nsdiff / CAST(60000000000 AS BIGINT) % 60 AS mi,

nsdiff / 1000000000 % 60 AS ss,

nsdiff % 1000000000 AS ns

FROM ( VALUES( CASE WHEN @dt1 > @dt2 THEN @dt2 ELSE @dt1 END,

CASE WHEN @dt1 > @dt2 THEN @dt1 ELSE @dt2 END,

CASE WHEN @dt1 < @dt2 THEN 1

WHEN @dt1 = @dt2 THEN 0

WHEN @dt1 > @dt2 THEN -1 END ) ) AS D(dt1, dt2, sgn)

CROSS APPLY ( VALUES( CAST(dt1 AS TIME), CAST(dt2 AS TIME),

DATEDIFF(yy, dt1, dt2),

DATEDIFF(mm, dt1, dt2),

DATEDIFF(dd, dt1, dt2) ) )

AS A1(t1, t2, yydiff, mmdiff, dddiff)

CROSS APPLY ( VALUES

( CASE WHEN DATEADD(yy, yydiff, dt1) > dt2 THEN 1 ELSE 0 END,

CASE WHEN DATEADD(mm, mmdiff, dt1) > dt2 THEN 1 ELSE 0 END,

CASE WHEN DATEADD(dd, dddiff, dt1) > dt2 THEN 1 ELSE 0 END ) )

AS A2(subyy, submm, subdd)

CROSS APPLY ( VALUES( CAST(86400000000000 AS BIGINT) * subdd

+ (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t2)

+ DATEPART(ns, t2))

- (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t1)

+ DATEPART(ns, t1)) ) )

AS A3(nsdiff);

GO

I use a series of CROSS APPLY operators to make the elements (columns) that are created by one operator available to subsequent operators, following *logical query processing* semantics. If you need a refresher of how the APPLY operator works and its logical query processing aspects, you can find those here.

The first step in the solution defines the derived table D with the columns dt1, dt2 and sgn in the FROM clause:

FROM ( VALUES( CASE WHEN @dt1 > @dt2 THEN @dt2 ELSE @dt1 END,

CASE WHEN @dt1 > @dt2 THEN @dt1 ELSE @dt2 END,

CASE WHEN @dt1 < @dt2 THEN 1

WHEN @dt1 = @dt2 THEN 0

WHEN @dt1 > @dt2 THEN -1 END ) ) AS D(dt1, dt2, sgn)

This step ensures that if the two inputs are different points in time, dt1 will hold the earlier one and dt2 the later one. This is important for the correctness of the calculation. Based on the column sgn you can tell what’s the sign of the result (1 when @dt1 is earlier than @dt2, -1 when @dt2 is earlier than @dt1, 0 when they are the same, and NULL when at least one is NULL).

The second step in the solution uses the CROSS APPLY operator to define the correlated derived table A1, with the columns t1, t2, yydiff, mmdiff and dddiff:

CROSS APPLY ( VALUES( CAST(dt1 AS TIME), CAST(dt2 AS TIME),

DATEDIFF(yy, dt1, dt2),

DATEDIFF(mm, dt1, dt2),

DATEDIFF(dd, dt1, dt2) ) )

AS A1(t1, t2, yydiff, mmdiff, dddiff)

The columns t1 and t2 are just the time parts extracted from dt1 and dt2, respectively. The columns yydiff, mmdiff and dddiff are the differences between dt1 and dt2 in terms of years, months and days, respectively. Each of these differences needs to be adjusted by subtracting one unit from it if the value of the immediate part below in dt2 is smaller than in dt1. For instance, suppose that the year-month value of dt1 is 1971-02 and the year-month value of dt2 is 2017-01. The unadjusted year difference between dt1 and dt2 (yydiff) is 46. However, since the month part in dt2 is smaller than the month part in dt1, you need to subtract one year from the year difference, resulting in 45 whole years. The third step in the solution computes the adjustment value for each of the three parts, like so:

CROSS APPLY ( VALUES

( CASE WHEN DATEADD(yy, yydiff, dt1) > dt2 THEN 1 ELSE 0 END,

CASE WHEN DATEADD(mm, mmdiff, dt1) > dt2 THEN 1 ELSE 0 END,

CASE WHEN DATEADD(dd, dddiff, dt1) > dt2 THEN 1 ELSE 0 END ) )

AS A2(subyy, submm, subdd)

The code uses the CROSS APPLY operator to define the correlated derived table A2, with the columns subyy, submm and subdd, which hold 1 if a unit needs to be subtracted from the corresponding part and 0 otherwise. The trick the code uses is to add the difference computed by step 2 to dt1, and if the result is greater than dt2, you know that you need to adjust the corresponding part by subtracting 1 unit. For example, if after adding yydiff years to dt1 the result is greater than dt2, subyy (year adjustment) is 1, otherwise 0.

The fourth step uses the CROSS APPLY operator to define a correlated derived table called A3 with the column nsdiff:

CROSS APPLY ( VALUES( CAST(86400000000000 AS BIGINT) * subdd

+ (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t2)

+ DATEPART(ns, t2))

- (CAST(1000000000 AS BIGINT) * DATEDIFF(ss, '00:00', t1)

+ DATEPART(ns, t1)) ) )

AS A3(nsdiff)

The nsdiff column holds the nanosecond difference between t1 and t2, adjusted by the number of nanoseconds in a day (86,400,000,000,000) if subdd is 1 (if t1 > t2). The computation of the number of nanoseconds in t1 and t2 is broken into the number of seconds in the value times number of nanoseconds in a second (1,000,000,000), plus the nanoseconds part of the value. The calculation is done in this manner to overcome the fact that the difference in nanoseconds between t1 and t2 may not fit in a 4-byte integer. If you’re running on SQL Server 2016 or later, the above code can be simplified using DATEDIFF_BIG, like so:

CROSS APPLY ( VALUES( CAST(86400000000000 AS BIGINT) * subdd

+ DATEDIFF_BIG(ns, t1, t2) ) ) AS A3(nsdiff)

The fifth and last step is to compute the result columns in the SELECT list, like so:

SELECT

sgn,

yydiff - subyy AS yy,

(mmdiff - submm) % 12 AS mm,

DATEDIFF(day, DATEADD(mm, mmdiff - submm, dt1), dt2) - subdd AS dd,

nsdiff / CAST(3600000000000 AS BIGINT) AS hh,

nsdiff / CAST(60000000000 AS BIGINT) % 60 AS mi,

nsdiff / 1000000000 % 60 AS ss,

nsdiff % 1000000000 AS ns

The column sgn was already computed in step 1.

The final year difference (result column yy) is yydiff adjusted by subyy.

The final month difference (result column mm) is the adjusted month difference (mmdiff - submm), modulo 12, since it’s the month difference after accounting for the year difference.

The final day difference (result column dd) is the day difference between dt1, advanced by the final month difference, and dt2, adjusted by subdd.

The final hour difference (result column hh) is the nanosecond difference (nsdiff) divided with integer division by the number of nanoseconds in an hour (3,600,000,000,000).

The final minute difference (result column mi) is the nanosecond difference (nsdiff) divided with integer division by the number of nanoseconds in a minute (60,000,000,000), modulo 60 to account for the hour difference.

The final second difference (result column ss) is the nanosecond difference (nsdiff) divided with integer division by the number of nanoseconds in a second (1,000,000,000), modulo 60 to account for the minute difference.

The final nanosecond difference (result column ns) is the nanosecond difference (nsdiff) modulo the number of nanoseconds in a second (1,000,000,000), to account for the second difference.

**Conclusion**

Date and time calculations often tend to involve a lot of trickiness. This month’s challenge was no exception. I provided a solution to computing the difference between two date and time values in a combination of parts ranging from the year down to the nanosecond. I find the task to also be a good exercise in logic. Hope you had fun with the puzzle.

## Discuss this Article 6

What about days when leap year is involved. When the two dates are 20170215 and 20170310 vs 20160215 and 20170310? Where are the days in each month incorporated into the logic? Some months have 30 days or 31, then there's 28 vs 29! I don't see that in your code. Thanks. Was just working on age in years and months yesterday.

Hi McSick,

My solution relies on DATEADD and DATEDIFF, both of which handle leap years correctly. For this reason, there's no need for explicit handling of leap years. For instance, the following query returns 23 days as the difference between the two input values since 2017 is a common year:

SELECT sgn, yy, mm, dd, hh, mi, ss, ns

FROM dbo.DATEDIFFPARTS('20170215', '20170310');

The following query correctly returns 24 days as the difference since 2016 is a leap year:

SELECT sgn, yy, mm, dd, hh, mi, ss, ns

FROM dbo.DATEDIFFPARTS('20160215', '20160310');

In your question you suggested using the input pair 20160215 and 20170310 to validate the solution:

SELECT sgn, yy, mm, dd, hh, mi, ss, ns

FROM dbo.DATEDIFFPARTS('20160215', '20170310');

This code returns 1 year and 23 days as the difference, which is correct. Adding one year to the first input brings you to 20170215. Then adding 23 more days brings you to the second input.

Hopefully this clarifies things. If you had different expectations from the solution, let me know what those are and your logic behind those.

Cheers,

Itzik

Very clever solution, Itzik.

The first time I solved it, I just applied brute force by incrementing the initial date with the previous calculation and producing a new date for the next step all the way till the second. Ended with multiple CROSS APPLY operators (11).

- Calculate the difference in years including the adjustment (1 or 0)

- Add previous calculation to @dt1 and produce @dt3

- Calculate the difference in months between @dt3 and @dt2 including adjustment

- Add previous calculation to @dt3 and produce @dt4

- ...

- All the way till the seconds

- For the nanoseconds I just used DATEDIFF

Curiously, when I turne on the include actual execution plan, the elapsed time is between 9 - 11 seconds in both 2012 and 2014 versions.

Cheers,

Alejandro

*****

DECLARE

@dt1 datetime2(7) = '19710212 12:00:00.0000001',

@dt2 datetime2(7) = '20170208 12:00:00.0000000';

SELECT

T1.yy,

T3.mm,

T5.dd,

T7.hh,

T9.mi,

T11.ss,

DATEDIFF(NANOSECOND, T12.dt8, @dt2) AS ns

FROM

(

SELECT R.diff_yy - CASE WHEN DATEADD(YEAR, R.diff_yy, @dt1) > @dt2 THEN 1 ELSE 0 END AS yy

FROM (VALUES (DATEDIFF(YEAR, @dt1, @dt2))) AS R(diff_yy)

) AS T1

CROSS APPLY

(

SELECT DATEADD(YEAR, T1.yy, @dt1) AS dt3

) AS T2

CROSS APPLY

(

SELECT R.diff_mm - CASE WHEN DATEADD(MONTH, R.diff_mm, T2.dt3) > @dt2 THEN 1 ELSE 0 END AS mm

FROM (VALUES (DATEDIFF(MONTH, T2.dt3, @dt2))) AS R(diff_mm)

) AS T3

CROSS APPLY

(

SELECT DATEADD(MONTH, T3.mm, T2.dt3) AS dt4

) AS T4

CROSS APPLY

(

SELECT R.diff_dd - CASE WHEN DATEADD(DAY, R.diff_dd, T4.dt4) > @dt2 THEN 1 ELSE 0 END AS dd

FROM (VALUES (DATEDIFF(DAY, T4.dt4, @dt2))) AS R(diff_dd)

) AS T5

CROSS APPLY

(

SELECT DATEADD(DAY, T5.dd, T4.dt4) AS dt5

) AS T6

CROSS APPLY

(

SELECT R.diff_hh - CASE WHEN DATEADD(HOUR, R.diff_hh, T6.dt5) > @dt2 THEN 1 ELSE 0 END AS hh

FROM (VALUES (DATEDIFF(HOUR, T6.dt5, @dt2))) AS R(diff_hh)

) AS T7

CROSS APPLY

(

SELECT DATEADD(HOUR, T7.hh, T6.dt5) AS dt6

) AS T8

CROSS APPLY

(

SELECT R.diff_mi - CASE WHEN DATEADD(MINUTE, R.diff_mi, T8.dt6) > @dt2 THEN 1 ELSE 0 END AS mi

FROM (VALUES (DATEDIFF(MINUTE, T8.dt6, @dt2))) AS R(diff_mi)

) AS T9

CROSS APPLY

(

SELECT DATEADD(MINUTE, T9.mi, T8.dt6) AS dt7

) AS T10

CROSS APPLY

(

SELECT R.diff_ss - CASE WHEN DATEADD(SECOND, R.diff_ss, T10.dt7) > @dt2 THEN 1 ELSE 0 END AS ss

FROM (VALUES (DATEDIFF(SECOND, T10.dt7, @dt2))) AS R(diff_ss)

) AS T11

CROSS APPLY

(

SELECT DATEADD(SECOND, T11.ss, T10.dt7) AS dt8

) AS T12

OPTION (RECOMPILE, MAXDOP 1);

Thanks Alejandro!

Hi Itzik,

One minor formatting question. When using CROSS APPLY to chain calculations, I tend to use:

CROSS APPLY ( SELECT

CASE WHEN Foo = 1 THEN 1 ELSE 0 END AS FooCalc,

CASE WHEN Bar = 1 THEN 1 ELSE 0 END AS BarCalc

) AS C1

You seem to use the following approach instead:

CROSS APPLY ( VALUES (

CASE WHEN Foo = 1 THEN 1 ELSE 0 END,

CASE WHEN Bar = 1 THEN 1 ELSE 0 END ) )

AS C1(FooCalc, BarCalc)

Is there a reason to use one over the other? I prefer the SELECT syntax because it keeps the column names with the calculations, rather than splitting them into a seperate section of the syntax, but if there's a performance reason to use VALUES I'd like to know.

Thanks!

Hi tovodeverett,

Both are algebrized the same. The SELECT syntax is supported since SQL Server 2005 and the VALUES clause since SQL Server 2008. I guess it's a matter of style preference. Personally, I find the VALUES clause more elegant and hence prefer to use it. If you find the SELECT option more suitable, that's just as good. :)

Cheers,

Itzik