MSSQL 計算兩日期間工作天數的高效函數（兩種實現）

需求

計算兩個日期之間的工作日天數（排除週六、週日），支援正向與逆向日期輸入，回傳整數（正數表示結束日在開始日之後，負數表示相反）。

方案一：集合運算法（效能較好，適合大範圍）

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[f_WorkDay]') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[f_WorkDay];
GO

CREATE FUNCTION dbo.f_WorkDay(
    @dt_begin DATETIME,  -- 計算起始日
    @dt_end   DATETIME   -- 計算結束日
) RETURNS INT
AS
BEGIN
    DECLARE @i INT;
    SELECT @i = ABS(DATEDIFF(DAY, @dt_begin, @dt_end));

    DECLARE @t TABLE (dt DATETIME);

    IF @dt_begin > @dt_end
        INSERT @t SELECT DATEADD(DAY, number, @dt_end)
        FROM master..spt_values
        WHERE number <= @i AND type = 'P';
    ELSE
        INSERT @t SELECT DATEADD(DAY, number, @dt_begin)
        FROM master..spt_values
        WHERE number <= @i AND type = 'P';

    -- (DATEPART(weekday) + @@DATEFIRST - 1) % 7  將週日=0, 週一=1...週六=6 統一映射
    RETURN (SELECT COUNT(*) FROM @t 
            WHERE (DATEPART(WEEKDAY, dt) + @@DATEFIRST - 1) % 7 BETWEEN 1 AND 5);
END
GO

-- 測試
SELECT dbo.f_WorkDay('2009-10-10', '2009-10-01');  -- 返回 7

原理：利用系統表 master..spt_values（type='P' 為 0-2047 連續整數）產生日期序列，再以模 7 運算過濾週一至週五。集合運算避免迴圈，效能優於逐日判斷。

方案二：迴圈累加法（邏輯直觀，適合教學）

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[f_WorkDay]') AND xtype IN (N'FN', N'IF', N'TF'))
    DROP FUNCTION [dbo].[f_WorkDay];
GO

CREATE FUNCTION dbo.f_WorkDay(
    @dt_begin DATETIME,
    @dt_end   DATETIME
) RETURNS INT
AS
BEGIN
    DECLARE @workday INT = 0, @i INT, @bz BIT, @dt DATETIME;

    IF @dt_begin > @dt_end
        SELECT @bz = 1, @dt = @dt_begin, @dt_begin = @dt_end, @dt_end = @dt;
    ELSE
        SET @bz = 0;

    WHILE @dt_begin <= @dt_end
    BEGIN
        SELECT @workday = CASE 
            WHEN (@@DATEFIRST + DATEPART(WEEKDAY, @dt_begin) - 1) % 7 BETWEEN 1 AND 5 
            THEN @workday + 1 ELSE @workday END,
        @dt_begin = DATEADD(DAY, 1, @dt_begin);
    END

    RETURN CASE WHEN @bz = 1 THEN -@workday ELSE @workday END;
END
GO

-- 測試
SELECT dbo.f_WorkDay('2009-10-10', '2009-10-01');  -- 返回 -7

關鍵點解析

• @@DATEFIRST：Session 級設定，定義週第一天（1=週一 ... 7=週日），預設美式 7（週日）。
• (DATEPART(WEEKDAY, dt) + @@DATEFIRST - 1) % 7：將任意語系環境下的 weekday 統一映射為 0=週日, 1=週一...6=週六。
• BETWEEN 1 AND 5：即週一至週五為工作日。
• 函數支援日期倒序輸入，回傳負值表示方向。

效能建議

1. 日期跨度 < 1 年：兩種方案差異微小。
2. 跨度 > 5 年或高頻呼叫：優先用方案一（集合運算），或預先建立「行事曆表」含 IsWorkDay 欄位，查詢直接 SUM(IsWorkDay) 極致快速。
3. 避免在大量資料列的查詢中逐列呼叫標量函數，改用 CROSS APPLY 或內聯表值函數（iTVF）。

小結

提供兩種相容 SQL Server 2005+ 的純 T-SQL 實現。生產環境建議採用方案一或行事曆表方案，兼顧效能與可維護性。