SQL存储过程如何精确计算两个日期之间的工作时长

先说一个明确的结论：凡是业务逻辑涉及节假日、调休安排以及固定工作时段的情况，千万不要指望用 DATEDIFF 一劳永逸。该函数既不识别法定假日，也不考虑上下班的具体时间范围，更无法处理跨天重叠的工作场景——最终算出的时长必然偏高，而且排查问题时往往耗费大量精力仍难以定位根源。

务必建立节假日表，且字段类型必须使用 DATE

千万不要用 DATETIME 来存储假期日期，否则 JOIN 操作时会因时分秒不匹配而遗漏判断，带来无尽烦恼。表结构至少需要包含以下两列：

• holiday_date DATE —— 主键，精确到日
• is_workday BIT —— 0 代表放假，1 代表调休上班（例如周日补班）

索引务必建立在 holiday_date 字段上，否则大范围查询时 LEFT JOIN 的性能会成为明显瓶颈。至于硬编码春节、国庆等固定假期的做法，等于每年都需要修改一次 SQL 脚本，上线前还得人工逐条核对，风险太高，没有团队愿意承担。

借助 CTE 批量生成日期，彻底放弃 WHILE 循环

一个常见的误区：在标量函数中使用 WHILE 循环，每过一天就执行一次 EXISTS(SELECT 1 FROM dbo.Holidays)。跨度三年就要发起上千次单行查询，CPU 瞬间满载，超时几乎不可避免。

正确的做法是利用递归 CTE 或数字表一次性生成全部中间日期。具体代码示例如下：

WITH dates AS (  SELECT DATEADD(DAY, n.n, CAST(@start_datetime AS DATE)) AS dt  FROM dbo.Numbers n  WHERE n.n BETWEEN 0 AND DATEDIFF(DAY, @start_datetime, @end_datetime))

如果开发环境中尚未建立 Numbers 表，可以临时借助 master..spt_values 来替代；但生产环境必须创建一张真实的数字表——它本质上是一个包含整数序列的辅助表，构建一次即可长期使用，非常划算。

逐日单独计算交集，然后 SUM 有效分钟数

假设工作时段设定为 08:00–20:00，那么就需要针对每一天单独截取与输入时间的重叠部分：

• work_start = CAST(dt AS DATETIME) + '08:00'
• work_end = CAST(dt AS DATETIME) + '20:00'
• actual_start = CASE WHEN @start_datetime > work_start THEN @start_datetime ELSE work_start END
• actual_end = CASE WHEN @end_datetime < work_end THEN @end_datetime ELSE work_end END

最后将 actual_start >= actual_end 的无效区间过滤掉，再用 SUM(DATEDIFF(MINUTE, actual_start, actual_end)) / 60.0 计算出小时数。如果直接使用 DATEDIFF(HOUR, @start, @end)，凌晨三点这样的时段也会被错误计入，显然不符合实际需求。

DATEPART(WEEKDAY, ...) 的输出结果受 SET DATEFIRST 影响

默认情况下 DATEFIRST = 7（周日对应 1），但某些连接配置可能将其改为 DATEFIRST = 1（周一对应 1），这时周六返回的是 6 还是 7 就难以确定了。直接写死 NOT IN (1,7) 的判断逻辑，在某些环境下会把周六误判为工作日。

稳妥的做法是在逻辑开头显式重置该设置：

SET DATEFIRST 1;

此后使用 DATEPART(WEEKDAY, d.dt) NOT IN (6,7) 来判断周末。不建议依赖 (DATEPART(...) + @@DATEFIRST - 2) % 7 这类绕来绕去的方案——调试时你根本看不出是哪一天被误判了。

实际上，真正的难点不在于逻辑编写，而在于长期维护：节假日表每年都需要更新，调休日期需要人工录入，CTE 生成的日期范围一旦超出表中已有的数据，结果就会在不知不觉中偏少。因此上线之前，务必使用包含调休、跨年、跨周末的边界案例进行全面验证，确保计算万无一失。