SQL Server 2005 純量函數實戰：計算兩日期間工作天數（排除週末）

函數用途

計算兩個日期之間的工作天數（週一至週五），自動排除週六、週日，支援開始日期大於結束日期的反向計算（回傳負數）。

完整函數程式碼

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 f_WorkDay(
    @dt_begin DATETIME,  -- 計算起始日期
    @dt_end   DATETIME   -- 計算結束日期
)
RETURNS INT
AS
BEGIN
    DECLARE @workday INT, @i INT, @bz BIT, @dt DATETIME;

    -- 處理開始日期大於結束日期的情況
    IF @dt_begin > @dt_end
    BEGIN
        SELECT @bz = 1, @dt = @dt_begin, @dt_begin = @dt_end, @dt_end = @dt;
    END
    ELSE
        SET @bz = 0;

    -- 計算總天數與完整週數的工作天
    SELECT @i = DATEDIFF(DAY, @dt_begin, @dt_end) + 1,
           @workday = @i / 7 * 5,
           @dt_begin = DATEADD(DAY, @i / 7 * 7, @dt_begin);

    -- 處理剩餘不足一週的天數
    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 = @dt_begin + 1;
    END

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

核心邏輯解析

使用範例

-- 基本用法
SELECT dbo.f_WorkDay('2024-01-01', '2024-01-31') AS WorkDays_Jan2024;
-- 結果：23（2024 年 1 月有 23 個工作日）

-- 反向計算
SELECT dbo.f_WorkDay('2024-01-31', '2024-01-01') AS Negative_WorkDays;
-- 結果：-23

-- 搭配資料表查詢
SELECT 
    project_name,
    start_date,
    end_date,
    dbo.f_WorkDay(start_date, end_date) AS planned_work_days
FROM projects
WHERE status = 'Active';

擴充建議：支援國定假日

目前函數僅排除週末。實務常需再排除國定假日，建議：

1. 建立 Holidays(holiday_date DATE PRIMARY KEY, description NVARCHAR(100)) 資料表。
2. 修改函數在 WHILE 迴圈中加入：
   AND @dt_begin NOT IN (SELECT holiday_date FROM Holidays)
3. 或改用集合運算：
   SELECT COUNT(*) FROM Calendar WHERE is_workday = 1 AND date BETWEEN @dt_begin AND @dt_end（需預建行事曆表）。

效能考量

• 純量函數在大量資料列查詢中會逐列執行，可能成為效能瓶頸。
• 高頻呼叫場景建議改用 內嵌表值函數 或 預先計算行事曆表 配合 JOIN。
• SQL Server 2016+ 可考慮 DATEDIFF_BIG 處理超大日期範圍。

此函數展示純量函數處理日期運算的典型模式，理解其邏輯與限制，有助於設計更高效的日期計算方案。