Oracle工作日计算函数number_of_days周日入参返回小数优化咨询

问题根因 #

非整数结果出现的核心原因是第二步完整周计算逻辑未做边界校验：当起始日期为周日时，计算得到的完整周区间可能出现不足1周的无效时长，直接执行/7*5运算就会得到小数结果，使用ROUND()属于临时补丁，容易引发其他边界场景的计算误差。

优化方案 #

方案1：最小改动修复原有逻辑 #

仅修改第二步的计算逻辑，增加完整周有效性判断即可从根源解决问题，无需调整其他逻辑：

CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
RETURN  NUMBER
IS  v_number_of_days NUMBER;
    first_week_day DATE := TO_DATE('31-12-2017', 'DD-MM-YYYY');
    v_full_start DATE;
    v_full_end DATE;
    v_full_weeks NUMBER;
BEGIN
-- 先计算完整周的起止边界
SELECT 
    CASE WHEN MOD(start_date - first_week_day, 7) > 1
        THEN start_date + 8 - MOD(start_date - first_week_day, 7)
        ELSE start_date END,
    CASE WHEN MOD(end_date - first_week_day, 7) < 7
        THEN end_date - MOD(end_date - first_week_day, 7)
        ELSE end_date END
INTO v_full_start, v_full_end
FROM DUAL;
-- 计算有效完整周数，若起始大于结束则为0
v_full_weeks := CASE WHEN v_full_start > v_full_end THEN 0 ELSE FLOOR((v_full_end - v_full_start +1)/7) END;

SELECT 
--step 1 统计首周剩余工作日
( CASE  WHEN MOD(start_date - first_week_day, 7) BETWEEN 2 AND 5
        THEN 6 - MOD(start_date - first_week_day, 7)
        ELSE 0  END )
+
--step 2 有效完整周直接乘每周5个工作日
v_full_weeks *5
+
--step3 统计尾周剩余工作日
( CASE  WHEN MOD(end_date - first_week_day, 7) BETWEEN 1 AND 6
        THEN CASE WHEN MOD(end_date - first_week_day, 7) = 6
            THEN MOD(end_date - first_week_day, 7) - 1
            ELSE MOD(end_date - first_week_day, 7) END
        ELSE 0 END  )
INTO    v_number_of_days
FROM    DUAL;
RETURN  v_number_of_days;
END;
/

方案2：更易维护的简洁实现 #

如果你的日期区间跨度不会超过几年，用生成日期序列再过滤的方案逻辑更清晰，基本不会出现边界bug：

CREATE OR REPLACE FUNCTION number_of_days(start_date IN DATE, end_date IN DATE)
RETURN NUMBER
IS
    v_work_days NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO v_work_days
    FROM (
        SELECT start_date + LEVEL -1 AS dt
        FROM DUAL
        CONNECT BY LEVEL <= end_date - start_date +1
    )
    -- 排除周六周日：MOD(dt - TO_DATE('31-12-2017','DD-MM-YYYY'),7) 0为周日，6为周六
    WHERE MOD(dt - TO_DATE('31-12-2017','DD-MM-YYYY'),7) NOT IN (0,6);
    RETURN v_work_days;
END;
/

测试验证 #

测试你给出的示例入参（2021-11-21为周日，2021-11-24为周三），两个方案均返回正确结果3。

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