Different DBMS systems have different ways of managing dates. As I’m doing a series of articles on dates, I thought I’d throw some notes down about calculating the difference between some of them.

Testing dataset

Picking up from my article on Dates In SQL Server: Create Sample Date Ranges I’ll use a sample from there to create a date range for testing and demonstration for this article:

--------------------------------------------------------------------------------
-- Dates table
--------------------------------------------------------------------------------
CREATE TABLE testing_date_list
(
    TheDate DATE NOT NULL PRIMARY KEY
);


--------------------------------------------------------------------------------
-- Custom Date Range Using Number sequence
--------------------------------------------------------------------------------

DECLARE @DateStart DATE = '01-DEC-17';
DECLARE @DateEnd   DATE = '07-FEB-18';
 
PRINT 'Start Date: ' + CONVERT(VARCHAR(10), @DateStart, 120);
PRINT 'Start End: '  + CONVERT(VARCHAR(10), @DateEnd,   120);

WITH number_tbl AS (
SELECT 0 AS a UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8 UNION ALL
SELECT 9
), number_sequences AS (
SELECT t1.a AS t1a,
       t2.a AS t2a,
       t3.a AS t3a,
       (t3.a + (10 * t2.a) + (100 * t1.a)) AS concatenate_calculation
  FROM number_tbl t1
  CROSS JOIN number_tbl t2
  CROSS JOIN number_tbl t3
)
INSERT
  INTO testing_date_list
      (TheDate)
SELECT CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) AS [TheDate]
  FROM number_sequences
 WHERE CAST(DateAdd(DAY, concatenate_calculation, @DateStart) AS DATE) <= @DateEnd
 ORDER BY concatenate_calculation
;

Addition, subtraction & rounding

Basic addition and subtraction requires the DateAdd() function, which has the syntax:

DATEADD (<datepart> , <number> , <date> )

While people can use simple arithmetic functions on dates in SQL Server, I’d always recommend against it. Regional settings and other changes can suddenly alter the behaviour of queries.

Rounding is commonly seen using a combination of DateDiff (more detail below) and DateAdd(). The takeaway is that to round in SQL server (historically) one calculated the difference in days (or whatever we are rounding to) between the current day (or whatever we want to round to), and 0. Then added that difference to 0. This has become the default way people do rounding, an alternative (more legible, but perhaps less performant) is to use DateFromParts():

DateFromParts(<year>, <month>, <day>)

A full set of examples are:

--------------------------------------------------------------------------------
-- Addition, Subtraction & Rounding
--------------------------------------------------------------------------------
SELECT [TheDate],
     --Adding & Subtracting
       DateAdd(DAY, 1, [TheDate]) AS TheDate_DateAdd_1_day,
       DateAdd(DAY,-1, [TheDate]) AS TheDate_DateAdd_sub_1_day,
     --Rounding
       DateAdd(MONTH, DateDiff(MONTH, 0, [TheDate]), 0)    AS TheDate_WholeMonth,
       DateAdd(YEAR,  DateDiff(YEAR,  0, [TheDate]), 0)    AS TheDate_WholeYear,
       DateFromParts(YEAR([TheDate]), MONTH([TheDate]), 1) AS TheDate_WholeMonth_DFP,
     --Subtracting and Rounding
       DateAdd(MONTH, -3, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS TheDate_ThreeMonthsBeforeStart,
       DateAdd(MONTH,-13, DateAdd(MONTH, DateDiff(MONTH, 0, GetDate()), 0)) AS TheDate_PriorYearMonthOnMonth   -- 1 year of months from last month
  FROM testing_date_list
;

Finding the difference

To answer questions like “how many days ago was X” or “How long did it take between @StartPoint and @EndPoint”. We can use DateDiff().

DATEDIFF (<datepart>, <startdate>, <enddate>)

This will give us an INT value of the number of <datepart> between the two dates. If you are expecting a lot back, consider DateDiffBig().

Here are some examples looking at dates:

--------------------------------------------------------------------------------
-- Difference
--------------------------------------------------------------------------------
SELECT [TheDate],
     --Difference
       DateDiff(DAY,   GetDate(), [TheDate]) AS DiffFromNow_Days,
       DateDiff(MONTH, GetDate(), [TheDate]) AS DiffFromNow_Months,
       DateDiff(YEAR,  GetDate(), [TheDate]) AS DiffFromNow_Years,
       DateDiff(HOUR,  GetDate(), [TheDate]) AS DiffFromNow_Hours
  FROM testing_date_list
;

Calculating duration

Another good example is finding out “how long did X take”:

--------------------------------------------------------------------------------
-- Difference between start and end
-- 750 seconds is 12.5 minits
--------------------------------------------------------------------------------
DECLARE @DateTimeStart DATETIME2(4) = DateAdd(SECOND, -750, GetDate());
DECLARE @DateTimeEnd   DATETIME2(4) = GetDate();

SELECT @DateTimeStart AS [start],
       @DateTimeEnd   AS [end],
       DateDiff(SECOND, @DateTimeStart, @DateTimeEnd) AS DiffInSeconds,
     --Note: the minutes are rounded up here
       DateDiff(MINUTE, @DateTimeStart, @DateTimeEnd) AS DiffInMinutes,
     --To format the time:
     -- - Calculate the number of seconds
     -- - Multiply that by 1000 so we get milliseconds
     -- - Add to 0
     -- - Only output the time portion
       CONVERT(VARCHAR, DateAdd(MILLISECOND, DateDiff(SECOND, @DateTimeStart, @DateTimeEnd) * 1000, 0), 114) AS FormattedTime
;

 

See it all in action over on rextester.com