自定义dbo.WorkingD函数计算工作日结果异常的原因排查

我创建了名为dbo.WorkingD的SQL函数，用于计算指定时间区间的工作日，但执行测试代码时输出结果不符合预期：

原函数代码 #

CREATE FUNCTION dbo.WorkingD (@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @TotalDays INT, @WorkDays INT

    SET @TotalDays = DATEDIFF(DAY, @StartDate, @EndDate) + 1 
    SET @WorkDays = @TotalDays - (DATEDIFF(WEEK, @StartDate, @EndDate) * 2) 

   --We exclude weekends (Saturday and Sunday)
IF DATEPART(WEEKDAY, @StartDate) = 1  --Sunday
    SET @WorkDays = @WorkDays - 1
IF DATEPART(WEEKDAY, @EndDate) = 7  -- Saturday
    SET @WorkDays = @WorkDays - 1
IF DATEPART(WEEKDAY, @StartDate) = 7  
    SET @WorkDays = @WorkDays - 1
IF DATEPART(WEEKDAY, @EndDate) = 1  
    SET @WorkDays = @WorkDays - 1

    RETURN @WorkDays
END;

测试代码 #

DECLARE @StartDate DATE = '2023-07-15'
DECLARE @EndDate DATE = '2023-07-23'

SELECT dbo.WorkingD(@StartDate, @EndDate) AS numar_zile_lucratoare;

预期结果为5，但实际输出3，仅保留其中两个IF判断时结果正确，请求解答。

错误原因分析 #

1. 初始计算逻辑缺陷
   DATEDIFF(WEEK, @StartDate, @EndDate)返回的是两个日期之间跨越的周边界数（比如以周日为周起始时，就是跨越的周日数量），直接乘以2作为周末总数的逻辑不严谨。例如测试案例中，2023-07-15（周六）到2023-07-23（周日）跨越了2个周起始日（7月16日、7月23日），此时DATEDIFF(WEEK)返回2，初始计算得到9 - 2*2 = 5，但这个值已经包含了部分周末的扣除。

2. 重复扣除周末天数
   原函数的四个IF判断会重复扣除周末：当起始日是周六（DATEPART=7）、结束日是周日（DATEPART=1）时，会触发两个IF各减1，导致5-2=3，这就是实际输出错误的原因。而仅保留两个IF时，刚好抵消了初始计算的误差，属于巧合，并非正确逻辑。

另外需要注意：DATEPART(WEEKDAY)的返回值受@@DATEFIRST设置影响（比如周日=1或周一=1），这也会导致判断逻辑的偏差。

修复后的函数实现 #

方案1：优化数学计算逻辑

CREATE FUNCTION dbo.WorkingD (@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    DECLARE @TotalDays INT = DATEDIFF(DAY, @StartDate, @EndDate) + 1
    DECLARE @StartWeekDay INT = DATEPART(WEEKDAY, @StartDate)
    DECLARE @EndWeekDay INT = DATEPART(WEEKDAY, @EndDate)
    DECLARE @FullWeeks INT = DATEDIFF(WEEK, @StartDate, @EndDate)

    -- 初始工作日：总天数减去整周的周末数
    DECLARE @WorkDays INT = @TotalDays - (@FullWeeks * 2)

    -- 调整起始日为周末的情况（避免重复扣除）
    IF @StartWeekDay IN (1, 7)
        SET @WorkDays -= 1

    -- 调整结束日为周末的情况，且起始日≠结束日（避免同一天重复减）
    IF @EndWeekDay IN (1, 7) AND @StartDate <> @EndDate
        SET @WorkDays -= 1

    -- 修正同一周内起始和结束都是周末的多扣问题
    IF @FullWeeks = 0 AND @StartWeekDay IN (1,7) AND @EndWeekDay IN (1,7)
        SET @WorkDays += 1

    RETURN @WorkDays
END;

方案2：枚举日期（逻辑更直观）

适合日期区间不大的场景，避免数学计算的边界错误：

CREATE FUNCTION dbo.WorkingD (@StartDate DATE, @EndDate DATE)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT COUNT(*)
        FROM (
            SELECT DATEADD(DAY, number, @StartDate) AS Date
            FROM master..spt_values
            WHERE type = 'P' AND number <= DATEDIFF(DAY, @StartDate, @EndDate)
        ) AS DateRange
        -- 根据@@DATEFIRST调整周末的判断值，此处假设周日=1，周六=7
        WHERE DATEPART(WEEKDAY, Date) NOT IN (1, 7)
    )
END;

内容的提问来源于stack exchange，提问作者Vlad Duta