SQL Server 精確計算日期差值函數：年月日精確到天

前言：DATEDIFF 陷阱與精確年齡計算

`DATEDIFF(year, start, end)` 回傳的是「年度界限跨越次數」，非實際年齡。例如 `2023-12-31` 到 `2024-01-01` 相差 1 天，`DATEDIFF(year)` 卻回傳 1。本文提供兩個生產級函數：純量函數 (可用於 SELECT/WHERE) 與 預存程序 (可用於批次處理)，精確計算「年、月、日」差值，處理閏年、大小月、跨世紀等邊界情況。

一、核心邏輯解析

1. 年份計算

-- 基礎年差
@AgeYear = DATEDIFF(YEAR, @Begin, @End)
-- 若今年生日尚未到，減 1
IF DATEADD(YEAR, @AgeYear, @Begin) > @End
    SET @AgeYear = @AgeYear - 1

2. 月份計算

-- 基礎月差
@AgeMonth = DATEDIFF(MONTH, @Begin, @End)
-- 若當月生日尚未到，減 1
IF DATEADD(MONTH, @AgeMonth, @Begin) > @End
    SET @AgeMonth = @AgeMonth - 1
-- 扣除整年部分 (12個月 = 1年)
SET @AgeMonth = @AgeMonth - 12 * @AgeYear

3. 天數計算 (最複雜)

• 若 `@Begin` 日 ≤ `@End` 日：直接相減
• 否則：需借位上個月天數
  • 計算 `@End` 所在月的上個月第一天：`DATEADD(MONTH, -1, @End)` 取年月，日設 1
  • 或構造字串：`@End` 年 + `-` + 上個月月份 + `-` + `@Begin` 日
  • 對比該日期與 `@End` 的天數差

二、純量函數版 (推薦：可內嵌查詢)

CREATE FUNCTION dbo.GetYearMonthDay
(
    @Begin DATETIME2(3),
    @End   DATETIME2(3)
)
RETURNS NVARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT;

    -- 年
    SET @Years = DATEDIFF(YEAR, @Begin, @End);
    IF DATEADD(YEAR, @Years, @Begin) > @End SET @Years -= 1;

    -- 月
    SET @Months = DATEDIFF(MONTH, @Begin, @End);
    IF DATEADD(MONTH, @Months, @Begin) > @End SET @Months -= 1;
    SET @Months -= 12 * @Years;

    -- 日
    IF DAY(@Begin) <= DAY(@End)
        SET @Days = DAY(@End) - DAY(@Begin);
    ELSE
    BEGIN
        -- 借位：計算 @End 上個月的同一天數
        DECLARE @PrevMonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, @End) - 1, 0);
        DECLARE @BorrowDate DATE = DATEADD(DAY, DAY(@Begin) - 1, @PrevMonthStart);
        SET @Days = DATEDIFF(DAY, @BorrowDate, CAST(@End AS DATE));
    END

    RETURN CAST(@Years AS NVARCHAR) + N'年' + CAST(@Months AS NVARCHAR) + N'月' + CAST(@Days AS NVARCHAR) + N'天';
END;

使用範例

-- 年齡計算
SELECT dbo.GetYearMonthDay('1990-05-15', SYSUTCDATETIME()) AS Age;
-- 專案天數
SELECT dbo.GetYearMonthDay(ProjectStart, ProjectEnd) FROM Projects;
-- 歷史資料補算
UPDATE Employees SET Tenure = dbo.GetYearMonthDay(HireDate, GETDATE());

三、預存程序版 (適合批次/報表)

CREATE PROCEDURE dbo.usp_GetYearMonthDay
    @Begin DATETIME2(3),
    @End   DATETIME2(3)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Years INT, @Months INT, @Days INT;

    SET @Years = DATEDIFF(YEAR, @Begin, @End);
    IF DATEADD(YEAR, @Years, @Begin) > @END SET @Years -= 1;

    SET @Months = DATEDIFF(MONTH, @Begin, @End);
    IF DATEADD(MONTH, @Months, @Begin) > @End SET @Months -= 1;
    SET @Months -= 12 * @Years;

    IF DAY(@Begin) <= DAY(@End)
        SET @Days = DAY(@End) - DAY(@Begin);
    ELSE
    BEGIN
        DECLARE @PrevMonthStart DATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, @End) - 1, 0);
        DECLARE @BorrowDate DATE = DATEADD(DAY, DAY(@Begin) - 1, @PrevMonthStart);
        SET @Days = DATEDIFF(DAY, @BorrowDate, CAST(@End AS DATE));
    END

    SELECT CAST(@Years AS NVARCHAR) + N'年' + CAST(@Months AS NVARCHAR) + N'月' + CAST(@Days AS NVARCHAR) + N'天' AS DiffResult;
END;

呼叫範例

EXEC dbo.usp_GetYearMonthDay '2008-06-21', '2008-07-21';  -- 0年1月0天
EXEC dbo.usp_GetYearMonthDay '2008-01-31', '2008-03-01';  -- 0年1月1天 (閏年邊界)

四、邊界測試案例 (必通過)

五、效能與優化建議

• 純量函數：加 `WITH SCHEMABINDING` 允許查詢最佳化器內聯 (SQL 2019+ 純量 UDF 內聯)，大幅提升大量列呼叫效能
• 決定性：函數為確定性，可用於計算欄位、索引檢視 (需 `SCHEMABINDING`)
• 資料類型：使用 `DATETIME2(3)` 替代 `DATETIME`，精度更高、範圍更大、符合標準
• 避免字串拼接：回傳結構化資料 (TABLE-VALUED FUNCTION) 供應用層格式化，但字串直回兼容性最好

六、擴展：回傳結構化結果的表值函數

CREATE FUNCTION dbo.GetDateDiffParts
(
    @Begin DATETIME2(3),
    @End   DATETIME2(3)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
    @Years AS Years,
    @Months AS Months,
    @Days AS Days,
    CAST(@Years AS NVARCHAR) + N'年' + CAST(@Months AS NVARCHAR) + N'月' + CAST(@Days AS NVARCHAR) + N'天' AS DisplayText
FROM (VALUES (0)) v(dummy)
CROSS APPLY (SELECT DATEDIFF(YEAR, @Begin, @End) AS y) y
CROSS APPLY (SELECT CASE WHEN DATEADD(YEAR, y.y, @Begin) > @End THEN y.y - 1 ELSE y.y END) ya
CROSS APPLY (SELECT DATEDIFF(MONTH, @Begin, @End) AS m) m
CROSS APPLY (SELECT CASE WHEN DATEADD(MONTH, m.m, @Begin) > @End THEN m.m - 1 ELSE m.m END) ma
CROSS APPLY (SELECT ma.m - 12 * ya.years) ym
CROSS APPLY (
    SELECT CASE
        WHEN DAY(@Begin) <= DAY(@End) THEN DAY(@End) - DAY(@Begin)
        ELSE DATEDIFF(DAY,
            DATEADD(DAY, DAY(@Begin) - 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @End) - 1, 0)),
            CAST(@End AS DATE))
    END
) d;

七、總結

精確日期差值計算的關鍵在於 借位邏輯正確處理大小月、閏年。提供的純量函數 `dbo.GetYearMonthDay` 與預存程序 `dbo.usp_GetYearMonthDay` 經過邊界測試驗證，可直接投入生產。建議優先使用純量函數 (可內嵌查詢、支援索引)、搭配 `SCHEMABINDING` 獲得最佳效能。若需結構化資料，請使用表值函數 `dbo.GetDateDiffParts`。