基于指定日期计算需排除的周五六日天数的SQL查询结果异常求解

问题根因 #

1. DATEPART(WEEKDAY)的返回值受数据库@@DATEFIRST参数影响，默认配置下周日返回1、周一返回2、周五返回6、周六返回7，原公式没有预先对齐星期基准，导致周六的计算逻辑异常。
2. 原公式对周六的计算不符合预期：2021-10-30为周六，DATEPART返回7，代入原公式得2 * ((7+5-2)/5) - 7/7 = 4 - 1 = 3，和预期的1不符。

修正方案 #

方案1：简化逻辑（推荐）

先将星期值转换为「周一为1、周日为7」的统一序列，直接计算即可得到预期结果：

SELECT 7 - ((DATEPART(WEEKDAY, '2021-10-29') + 5) % 7 + 1)
SELECT 7 - ((DATEPART(WEEKDAY, '2021-10-30') + 5) % 7 + 1)
SELECT 7 - ((DATEPART(WEEKDAY, '2021-10-31') + 5) % 7 + 1)

三个查询的返回结果依次为2、1、0，完全匹配预期。

方案2：基于原公式调整

如果需要保留原公式的结构，修改后半段的计算逻辑即可：

SELECT 2 * ((DATEPART(WEEKDAY, '2021-10-29') + 5 - 2) / 5) - CASE WHEN DATEPART(WEEKDAY, '2021-10-29') = 7 THEN 3 WHEN DATEPART(WEEKDAY, '2021-10-29') = 1 THEN 0 ELSE DATEPART(WEEKDAY, '2021-10-29') /7 END

替换对应日期后也可得到正确结果。

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