Correctly Calculating Datetime Differences

The next query, at callout A, accepts D5 as its input and generates the final desired result, which Table 2 shows. The query uses simple integer division (/) and modulo (%) to calculate the hour (h), minute (mi), and second (s) units based on the original seconds difference value you received from derived table D5. The query also calculates the millisecond difference (ms) by subtracting the milliseconds unit of from_ts from that of 1000 plus the milliseconds unit of to_ts modulo 1000.The reason for adding 1000 to the ms unit of to_ts, then calculating the modulo by 1000, is to accommodate a case in which the ms unit of to_ts is smaller than the ms unit of from_ts.

Encapsulating Logic in a Function
You now have a way to correctly calculate differences between two datetime values. And you can encapsulate this logic in a function that accepts two datetime values as inputs. You just need to determine how to return the output. You can create a table-valued function and return the different datetime elements and the sign of the result in different columns of a result row. Or, you can create a scalar function that concatenates all output elements in a single character string. Listing 3 shows an example of a scalar function that calculates datetime differences.

The only change you need to make to the original solution is to add a layer that formats the output as a scalar value, as callout A in Listing 3 shows.

To test the function, run the following code:

SELECT dbo.fn_datediff 
  ('20030321 
14:27:12.233', 
  '20060115 
11:45:22.263'); 

You'll receive +0002-09-24 21:18:10.030 as the output, meaning that the difference between the two inputs is positive, 2 years, 9 months, 24 days, 21 hours, 18 minutes, 10 seconds, and 30ms. To verify that the result is correct,use DATEADD() to add all elements to the @from_ts input, and note that you get the following @to_ts input:

SELECT 
  DATEADD(ms, 30, 
   DATEADD(second, 10,
    DATEADD(minute, 18, DATEADD(hour, 21, 
     DATEADD(day, 24, 
     DATEADD(month, 9, 
     DATEADD(year, 2,
     '20030321 
   14:27:12.233'))))))); 

As with many problems I cover in this column, solving the datetime-differences challenge involves a lot of logic. To stretch your logic muscles, remember to check out this month's Logical Puzzle, page XX. And don't miss the new section of this column, "Catch That Bug!"

Itzik Ben-Gan (itzik@solidqualitylearning.com), a mentor at Solid Quality Learning, teaches, lectures, and consults internationally. He manages the Israeli SQL Server Users Group, is a SQL Server MVP, and is a coauthor of Advanced Transact-SQL for SQL Server 2000 (Apress).