SQL 計算兩日期間工作日數：純 T-SQL 標量函數實現（周一至周五）

業務常需計算兩日期間的工作日數（排除週末）。本文提供一個健壯的標量函數 dbo.CalcWorkHours，輸入起止日期，返回工作日數 × 8 小時，核心邏輯基於 DATEPART(dw, ...) 與 DATEFIRST 設定，兼容跨週、同週、邊界場景。

函數完整代碼

CREATE FUNCTION dbo.CalcWorkHours(@bdate DATETIME, @edate DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @hours INT;

    -- 強制要求：週一為每周第一天（DATEFIRST=1）
    IF @@DATEFIRST <> 1 OR @bdate > @edate RETURN -1;

    SELECT @hours = 
        -- 情況 1：起止日期在同一周內
        CASE 
            WHEN DATEPART(wk, @edate - 1) - DATEPART(wk, @bdate) = 0 THEN
                CASE 
                    WHEN DATEPART(dw, @bdate) > 5 THEN 0                                    -- 起始日是週末
                    WHEN DATEPART(dw, @edate - 1) > 5 THEN 6 - DATEPART(dw, @bdate)       -- 結束日落在週末，截取到週五
                    ELSE DATEPART(dw, @edate - 1) - DATEPART(dw, @bdate) + 1              -- 正常同周工作日差
                END
            -- 情況 2：跨周
            ELSE
                -- 中間完整周數 × 5
                (DATEDIFF(dd, @bdate, @edate) - (8 - DATEPART(dw, @bdate)) - DATEPART(dw, @edate - 1)) / 7 * 5
                -- 首周剩餘工作日
                + CASE WHEN DATEPART(dw, @bdate) < 6 THEN 6 - DATEPART(dw, @bdate) ELSE 0 END
                -- 尾周工作日
                + CASE WHEN DATEPART(dw, @edate - 1) > 5 THEN 5 ELSE DATEPART(dw, @edate - 1) END
        END * 8;  -- 轉換為工時（8小時/天）

    RETURN @hours;
END
GO

核心邏輯解析

1. 前置條件：SET DATEFIRST 1（週一=1 ... 週日=7），否則 DATEPART(dw) 含義錯位。
2. 同周快速路徑：直接用周幾序數相減，處理週末截斷邊界。
3. 跨周分解公式：
   • 總天數 - 首周尾部天數 - 尾周頭部天數 = 中間完整周天數 → /7 * 5 得中間工作日。
   • 首周工作日 = 6 - 起始周幾（若起始≤週五）。
   • 尾周工作日 = MIN(結束周幾, 5)。
4. @edate - 1：傳入的結束日期通常為「截止日（含）」，內部轉為「截止日（不含）」計算，避免多算一天。

測試用例

-- 設置環境
SET DATEFIRST 1;
GO

-- 1. 同周內：週三到週五 = 3 天
SELECT dbo.CalcWorkHours('2024-01-03', '2024-01-06'); -- 返回 24 (3*8)

-- 2. 同周含週末：週五到下週一 = 1 天（僅週五）
SELECT dbo.CalcWorkHours('2024-01-05', '2024-01-09'); -- 返回 8

-- 3. 跨周：週三到下週二 = 7 天
SELECT dbo.CalcWorkHours('2024-01-03', '2024-01-10'); -- 返回 56

-- 4. 起始是週末：週六到下週三 = 4 天（週一~週四）
SELECT dbo.CalcWorkHours('2024-01-06', '2024-01-11'); -- 返回 32

-- 5. 異常輸入
SELECT dbo.CalcWorkHours('2024-01-10', '2024-01-05'); -- 返回 -1 (起始>結束)
SELECT dbo.CalcWorkHours('2024-01-01', '2024-01-10'); -- 若未 SET DATEFIRST 1 也返回 -1

擴展建議

• 節假日排除：建立 Holidays(date, is_workday) 表，函數中 LEFT JOIN 扣除法定假日、補班日。
• 班次支持：增加 @shift_id 參數，關聯班次日曆表，計算實際工時而非固定 8h。
• 內聯表值函數 (iTVF)：為提升大批量調用性能，可改寫為 RETURNS TABLE 無 BEGIN/END，優化器可內聯展開。

此函數無循環、無游標、純集合運算，單次調用微秒級，適合報表、考勤、SLA 計算等高頻場景。