將日期範圍轉換為間隔描述

最近的一個項目中的一項要求是報告資源何時會被完全消耗。除了用盡日曆日期外，我還被要求以類似英文的格式顯示剩餘時間，例如“1 year, 3 months to go”。

內置DATEDIFF函式

返回指定開始日期和結束日期之間跨越的指定日期部分邊界的計數。

如果按原樣使用，可能會產生誤導或混淆的結果。例如，使用 YEAR 的間隔將顯示 1999-12-31 (YYYY-MM-DD) 和 2000-01-01 相隔一年，而常識會說這些日期僅相隔 1 天。相反，使用 DAY 1999-12-31 和 2010-12-31 的間隔相隔 4,018 天，而大多數人會將“11 年”視為更好的描述。

從天數開始計算月份和年份，容易出現閏年和月份大小錯誤。

我想知道如何在各種 SQL 方言中實現這一點？範例輸出包括：

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)

我碰巧使用的是 SQL Server 2008R2，但我有興趣了解其他方言如何處理這個問題。

以下解決方案適用於 SQL Server。該方法與Serg 的方法相似，因為查詢僅使用 DATEADD 和 DATEDIFF 函式。但是，它不考慮負間隔（FromDate > ToDate），它從總月差中得出年份和月份：

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)
;

輸出：

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 

引用自：https://dba.stackexchange.com/questions/138080