T -SQL programmers share a basic and surprisingly challenging problem: correctly calculating the difference between two datetime values. SQL Server doesn't provide a built-in solution for this task, so you have to create your own.
Although SQL Server's DATEDIFF() function lets you calculate the difference between two datetime values in terms of a specified unit (e.g., year, month, day), DATE DIFF() considers only the specified unit and those higher in the temporal hierarchy— not lower units. For example, if you specify a unit of day, SQL Server will consider only the year, month, and day and ignore lower units (hour, minute, second, millisecond). Notice the following query, which asks for the difference in years between two values that are just 3ms apart:
SELECT DATEDIFF(year, '20051231 23:59:59.997',
'20060101 00:00:00.000')
SQL Server returns an incorrect value of 1 (instead of 0) for the number of years between these values because the function doesn't check units lower than year but rather simply subtracts 2005 from 2006.
Let's look at how to get the correct datetime difference, taking all units into consideration. In other words, given two timestamps—from_ts and to_ts—let's see how to return the difference in terms of years, months, days, hours, minutes, seconds, and milliseconds.
Test Your Solution First
Set up the datetime-differences problem by running the code in Listing 1 to create the TimeStamps table and populate it with sample data. Table 1 shows the Time-Stamps table's content. Each row contains an integer key (keycol) and a pair of timestamps (from_ts and to_ts). The task is to correctly calculate the differences between the timestamp pairs, considering all possible datetime units, and produce the desired result that Table 2 shows.
Before looking at my solution, take some time to try to solve the problem yourself. Your solution should handle cases in which from_ts is later than to_ts, in which case your code should generate a negative result. The sgn result column should identify whether the result is positive (1) or negative (-1). (Note that the outputs I show omit the from_ts and to_ts values to save space; you can correlate these values back to the TimeStamps table based on keycol.)
Step-by-Step Calculations
Now, let's walk through my solution, which Listing 2 shows. First, consider the innermost query at callout F in Listing 2, which generates the derived table D1, shown in Table 3. This query uses simple CASE expressions to achieve its purpose: to place the larger value of the timestamp pair (from_ts, to_ts) in the result column to_ts and to place the smaller value in from_ts. This operation allows for simpler calculations later, guaranteeing that from_ts is smaller than or equal to to_ts. The sgn result column will hold a value of 1 if from_ts is less than or equal to to_ts and -1 if from_ts is greater than to_ts.
Callout E shows the query that accepts D1 as its input, then generates derived table D2, which Table 4 shows. This query uses the DATEDIFF() function to calculate the date-unit differences (i.e., year, month, day). Remember that the unit difference that DATEDIFF() calculates might have an offset of 1 from the correct value because the function doesn't consider units lower than the unit specified in the temporal hierarchy; the next step will take care of that offset.
The query at callout D accepts D2 as its input and generates derived table D3, which Table 5 shows. This query uses a CASE expression for each date unit, adding the corresponding unit difference you received from the previous query to the from_ts value. If the result is greater than to_ts, the difference that DATEDIFF() calculated was greater than the correct value by 1, so the code subtracts 1 from the difference. This logic, which fixes the inaccuracy in DATEDIFF()'s calculation, is a key element of the solution.
Why not use the same logic to calculate the difference for the time units in the timestamp values? The reason is that when you get down to the time units, the difference between two timestamps might be higher than an integer can hold. In such cases, the calculation would overflow, so you need to handle the time units separately.
Calculating Time Elements
Now that you've calculated the correct differences for the date units, you need to return from each unit only the portion that the higher-level unit doesn't cover. For example, say you're given the timestamps 20030321 14:27:12.233 and 20060115 11:45:22.263. The calculations in the query at callout D in Listing 2 will yield 2 years, 33 months, and 1030 days, each correct independently. But to show the result in combined units, you need to return only the portion of months after subtracting the higher-level unit, which in this case is 9 months (33 months ? 2 years). Similarly, you want to return only the portion of days after subtracting the years and months, which is 24 days in our example (1030 days ? (2 years and 9 months)). Eventually, you'll return a difference of 2 years, 9 months, and 24 days (plus lower units). The next steps return the date elements and prepare the inputs you need to calculate the time elements.
The query at callout C accepts D3 as its input and generates the derived table D4, which Table 6 shows. This query simply shifts from_ts forward by each of the three date-unit differences (y, m, and d), generating the values y_ts, m_ts and d_ts, respectively. The solution will use each of these timestamps as an anchor to return only the relevant portion of a date unit.
The query at callout B accepts D4 as its input and generates the derived table D5, which Table 7, page 26, shows. This query returns only the relevant portion of each date unit, subtracting the difference between to_ts and the higher-level anchor from the independent date unit. The query also calculates the difference in seconds (s) between the day anchor and to_ts. The listing will use s in the next step to calculate all time elements except the milliseconds element.