如何计算两个日期差值并排除周二与联邦节假日？

要解决这个问题，核心是遍历日期区间内的每一天，逐一判断是否需要排除，最后统计符合条件的天数。CASE WHEN只能处理单条记录的字段，没法遍历区间内的所有日期，所以得用生成日期序列+关联过滤的方式实现。

假设表结构 #

先明确两个表的结构（你可以根据实际表名/字段名调整）：

• date_pairs：存储需要计算的日期对

  id | start_date  | end_date
  ---|-------------|-------------
  1  | 2023-09-29  | 2023-10-03
  

• calendar：存储每日的星期和节假日信息

  date       | weekday_name | is_federal_holiday
  -----------|--------------|--------------------
  2023-09-29 | Friday       | false
  2023-09-30 | Saturday     | false
  2023-10-01 | Sunday       | false
  2023-10-02 | Monday       | false
  2023-10-03 | Tuesday      | false
  

通用SQL解决方案（基于递归CTE） #

用递归公共表表达式（CTE）生成日期区间内的所有日期，再关联calendar表过滤掉周二和联邦节假日，最后统计有效天数：

WITH date_range AS (
    -- 递归起始：取每条记录的start_date
    SELECT 
        id,
        start_date AS current_date,
        end_date
    FROM date_pairs
    UNION ALL
    -- 递归生成后续日期，直到达到end_date
    SELECT 
        id,
        DATE_ADD(current_date, INTERVAL 1 DAY) AS current_date,
        end_date
    FROM date_range
    WHERE current_date < end_date
)
SELECT
    dr.id,
    dp.start_date,
    dp.end_date,
    COUNT(*) AS valid_days
FROM date_range dr
JOIN date_pairs dp ON dr.id = dp.id
LEFT JOIN calendar c ON dr.current_date = c.date
-- 过滤条件：不是周二 且 不是联邦节假日
WHERE 
    c.weekday_name != 'Tuesday'
    AND c.is_federal_holiday = false
GROUP BY dr.id, dp.start_date, dp.end_date;

针对你的示例验证 #

以2023-09-29至2023-10-03为例：

• 生成的日期区间是2023-09-29、2023-09-30、2023-10-01、2023-10-02、2023-10-03
• 过滤掉2023-10-03（周二）后，若这段时间无联邦节假日，最终有效天数为4。如果你的示例结果是2天，可能是需求为仅计算工作日且排除周二/节假日，或是日期区间计数规则为start_date到end_date的前一天，可调整递归中的WHERE current_date < end_date为current_date <= end_date或反过来适配需求。

优化方案：如果已有完整日历表 #

如果calendar表已经包含了所有需要的日期，也可以直接用calendar表筛选日期区间，不用递归生成：

SELECT
    dp.id,
    dp.start_date,
    dp.end_date,
    COUNT(*) AS valid_days
FROM date_pairs dp
JOIN calendar c 
    ON c.date BETWEEN dp.start_date AND dp.end_date
WHERE 
    c.weekday_name != 'Tuesday'
    AND c.is_federal_holiday = false
GROUP BY dp.id, dp.start_date, dp.end_date;

数据库差异说明 #

不同数据库的日期函数略有不同：

• MySQL：用DATE_ADD(current_date, INTERVAL 1 DAY)
• PostgreSQL：用current_date + INTERVAL '1 day'
• SQL Server：用DATEADD(day, 1, current_date)

根据你使用的数据库调整即可。

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