Database Agnostic – How to Convert Date Range to Interval Description

database-agnosticdate format

A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, something like "1 year, 3 months to go."

The built-in DATEDIFF function

Returns the count … of the specified datepart boundaries crossed between the specified startdate and enddate.

If used as-is this could produce misleading or confusing results. For example, using an interval of YEAR would show 1999-12-31 (YYYY-MM-DD) and 2000-01-01 to be one year apart whereas common sense would say these dates are separated by only 1 day. Conversely using an interval of DAY 1999-12-31 and 2010-12-31 are separated by 4,018 days while most people would see "11 years" as a better description.

Starting from the number of days and calculating months and years from there would be prone to leap year and size-of-month errors.

I got to wondering how this could be implemented in the various SQL dialects? Example output includes:

create table TestData(
    FromDate date not null,
    ToDate date not null,
    ExpectedResult varchar(100) not null); -- exact formatting is unimportant

insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
       ('1999-12-31', '2000-01-01', '1 day'),
       ('2000-01-01', '2000-02-01', '1 month'),
       ('2000-02-01', '2000-03-01', '1 month'),              -- month length not important
       ('2000-01-28', '2000-02-29', '1 month, 1 day'),       -- leap years to be accounted for
       ('2000-01-01', '2000-12-31', '11 months, 30 days'),
       ('2000-02-28', '2000-03-01', '2 days'),
       ('2001-02-28', '2001-03-01', '1 day'),                -- not a leap year
       ('2000-01-01', '2001-01-01', '1 year'),
       ('2000-01-01', '2011-01-01', '11 years'),
       ('9999-12-30', '9999-12-31', '1 day'),                -- catch overflow in date calculations
       ('1900-01-01', '9999-12-31', '8099 years 11 months 30 days');  -- min(date) to max(date)

I happen to be using SQL Server 2008R2 but I am interested to learn how other dialects would handle this.

Best Answer

The following solution is for SQL Server. The approach is similar to Serg's in that the query uses only the DATEADD and DATEDIFF functions. It does not, however, account for negative intervals (FromDate > ToDate), and it derives years and months from the total month difference:

WITH
  MonthDiff AS
  (
    SELECT
      t.FromDate,
      t.ToDate,
      t.ExpectedResult,
      Months = x.Months - CASE WHEN DAY(t.FromDate) > DAY(t.ToDate) THEN 1 ELSE 0 END
    FROM
      dbo.TestData AS t
      CROSS APPLY (SELECT DATEDIFF(MONTH, t.FromDate, t.ToDate)) AS x (Months)
  )
SELECT
  t.FromDate,
  t.ToDate,
  t.ExpectedResult,
  Result = ISNULL(NULLIF(ISNULL(x.Years  + CASE x.Years  WHEN '1' THEN ' year '  ELSE ' years '  END, '')
                       + ISNULL(x.Months + CASE x.Months WHEN '1' THEN ' month ' ELSE ' months ' END, '')
                       + ISNULL(x.Days   + CASE x.Days   WHEN '1' THEN ' day '   ELSE ' days '   END, ''), ''), '0 days')
FROM
  MonthDiff AS t
  CROSS APPLY
  (
    SELECT
      CAST(NULLIF(t.Months / 12, 0) AS varchar(10)),
      CAST(NULLIF(t.Months % 12, 0) AS varchar(10)),
      CAST(NULLIF(DATEDIFF(DAY, DATEADD(MONTH, t.Months, t.FromDate), t.ToDate), 0) AS varchar(10))
  ) AS x (Years, Months, Days)
;

Output:

FromDate    ToDate      ExpectedResult                 Result
----------  ----------  -----------------------------  -----------------------------
1999-12-31  1999-12-31  0 days                         0 days
1999-12-31  2000-01-01  1 day                          1 day 
2000-01-01  2000-02-01  1 month                        1 month 
2000-02-01  2000-03-01  1 month                        1 month 
2000-01-28  2000-02-29  1 month, 1 day                 1 month 1 day 
2000-01-01  2000-12-31  11 months, 30 days             11 months 30 days 
2000-02-28  2000-03-01  2 days                         2 days 
2001-02-28  2001-03-01  1 day                          1 day 
2000-01-01  2001-01-01  1 year                         1 year 
2000-01-01  2011-01-01  11 years                       11 years 
9999-12-30  9999-12-31  1 day                          1 day 
1900-01-01  9999-12-31  8099 years 11 months 30 days   8099 years 11 months 30 days

Related Solutions

Mysql – Convert Oracle Query for Date to MySQL queries

Your second query can be converted to MySQL in two ways.

First one is simple query, that adds interval of days to compared date, depending on which dayofweek it is. It's not the most elegant solution, and different days of week require different values (or selects). Code below uses CURDATE as a starting value, query for each column can be used separately:

SELECT
 DATE(CURDATE()) AS now_date,
 DATE_ADD(DATE(CURDATE()), INTERVAL (9 - IF(DAYOFWEEK(CURDATE()) < 2, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_monday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (10 - IF(DAYOFWEEK(CURDATE()) < 3, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_tuesday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (11 - IF(DAYOFWEEK(CURDATE()) < 4, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_wednesday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (12 - IF(DAYOFWEEK(CURDATE()) < 5, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_thursday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (13 - IF(DAYOFWEEK(CURDATE()) < 6, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_friday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (14 - IF(DAYOFWEEK(CURDATE()) < 7, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_saturday,
 DATE_ADD(DATE(CURDATE()), INTERVAL (8 - DAYOFWEEK(CURDATE())) DAY) AS next_sunday;

Code found and copied from old GizmoLA post's comment. Similar one, just for Sunday, was explained on StackOverflow.

Second one is more universal, but requires creating function. In this case it's code could look like this one, found on SQLines post:

CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20))
 RETURNS DATETIME
BEGIN
 DECLARE start DATETIME;
 DECLARE i INT;

 -- Select the next date
 SET start = ADDDATE(start_date, 1);
 SET i = 1;

 days: LOOP
       -- Compare the day names
    IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN    
     LEAVE days;
    END IF;

    -- Select the next date
    SET start = ADDDATE(start, 1);
    SET i = i + 1;

    -- Not valid weekday specified
    IF i > 7 THEN
     SET start = NULL;
     LEAVE days;
    END IF;

 END LOOP days;

 RETURN start;
END;

Usage (show next sunday):

SELECT NEXT_DAY(NOW(), 'Sun');

You can also use NEXT_DAY and SUBDATE functions to get the previous date:

SELECT SUBDATE(NEXT_DAY(NOW(), 'Sun'), 7);

Sql-server – Convert a date to yyyymmdd format

By default, as documented in MSDN, if no length is specified for varchar it will default to 30 when using CAST or CONVERT and will default to 1 when declared as a variable.

To demonstrate, try this :

DECLARE @WithLength varchar(3),@WithoutLength varchar;
SET @WithLength = '123';
SET @WithoutLength = '123';

SELECT @WithLength,@WithoutLength

This is very dangerous, as SQL Server quietly truncates the value, without even a warning and can lead to unexpected bugs.

However, coming to the topic in question, in the given scenario, with or without length does not make any difference between the two statements and I am unable to see the 2 trailing spaces that you are talking about. Try this:

SELECT CONVERT(varchar(8), GETDATE(), 112) 
UNION ALL 
SELECT DATALENGTH(CONVERT(varchar(8), GETDATE(), 112)) 
UNION ALL 
SELECT CONVERT(varchar, GETDATE(), 112)
UNION ALL
SELECT DATALENGTH(CONVERT(varchar, GETDATE(), 112))

You will notice that the DATALENGTH() function returns 8 in both cases.

Raj

Best Answer

Related Solutions

Mysql – Convert Oracle Query for Date to MySQL queries

Sql-server – Convert a date to yyyymmdd format

Related Question