如何在Oracle中计算指定日期区间内周六和周日的总天数？

方法一：生成日期序列逐天判断 #

这种方法逻辑直观，适合小范围日期区间，通过生成区间内的每一天，筛选出周六和周日后统计数量：

SELECT COUNT(*) AS weekend_days
FROM (
    SELECT start_date + LEVEL - 1 AS dt
    FROM (
        SELECT TO_DATE('01-12-2022','dd-mm-yyyy') AS start_date, SYSDATE AS end_date
        FROM dual
    )
    CONNECT BY LEVEL <= end_date - start_date + 1
)
WHERE TO_CHAR(dt, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') IN ('SAT', 'SUN');

方法二：数学公式计算（高效适配大区间） #

如果日期跨度很大，生成序列的方式效率偏低，可通过数学公式直接推导周末天数：

SELECT
    (full_weeks * 2) +
    GREATEST(0, LEAST(remaining_days, 7 - start_weekday + 1)) +
    GREATEST(0, LEAST(remaining_days - (7 - start_weekday + 1), 1)) AS weekend_days
FROM (
    SELECT
        start_date,
        end_date,
        end_date - start_date + 1 AS total_days,
        TRUNC((end_date - start_date + 1) / 7) AS full_weeks,
        MOD(end_date - start_date + 1, 7) AS remaining_days,
        TO_CHAR(start_date, 'D', 'NLS_DATE_LANGUAGE=ENGLISH') AS start_weekday -- 1=周日, 2=周一...7=周六
    FROM (
        SELECT TO_DATE('01-12-2022','dd-mm-yyyy') AS start_date, SYSDATE AS end_date
        FROM dual
    )
);

注意事项 #

• 加入NLS_DATE_LANGUAGE=ENGLISH是为了避免数据库语言设置不同导致星期判断出错，确保跨环境结果一致。
• 小日期区间优先选方法一，逻辑简单易调试；大区间优先选方法二，计算效率更高。

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