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.
