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

必须建节假日表并用CTE生成日期范围，结合每日工作时段交集计算有效工时，显式设置DATEFIRST避免周末误判。

不能只用 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 生成的日期范围一旦超出现有表数据，结果就静默变少。上线前务必用含调休、跨年、含周末的边界案例跑一遍验证。