Date-Format
將日期範圍轉換為間隔描述
最近的一個項目中的一項要求是報告資源何時會被完全消耗。除了用盡日曆日期外,我還被要求以類似英文的格式顯示剩餘時間,例如“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