SQL Server 2019中计算排除周末的datetime2时间差（含时分秒）

以下是针对需求的完整解决方案，支持正负时间差计算，输出指定格式并处理时分秒细节：

-- 创建测试临时表
CREATE TABLE #temptable(
start_datetime datetime2,
end_datetime datetime2
)
Insert into #temptable(start_datetime, end_datetime) values ('2022-10-31 10:30:52', '2022-11-02 23:47:55')
Insert into #temptable(start_datetime, end_datetime) values ('2022-11-01 08:30:46', '2022-11-03 02:59:27')
-- 新增负时间差测试用例
Insert into #temptable(start_datetime, end_datetime) values ('2022-11-03 02:59:27', '2022-11-01 08:30:46')

WITH cte AS (
    SELECT 
        start_datetime,
        end_datetime,
        -- 标记时间差正负方向：1为正，-1为负
        SIGN(DATEDIFF(SECOND, start_datetime, end_datetime)) AS time_sign,
        -- 统一取较早时间作为计算起始，较晚时间作为计算结束，简化逻辑
        CASE WHEN start_datetime <= end_datetime THEN start_datetime ELSE end_datetime END AS dt_start,
        CASE WHEN start_datetime <= end_datetime THEN end_datetime ELSE start_datetime END AS dt_end
    FROM #temptable
),
workday_count AS (
    SELECT
        start_datetime,
        end_datetime,
        time_sign,
        dt_start,
        dt_end,
        -- 统计时间范围内的工作日总数（包含起始和结束日）
        (SELECT COUNT(*) 
         FROM (
             SELECT DATEADD(DAY, nums.n, CAST(dt_start AS DATE)) AS work_day
             FROM (
                 SELECT TOP (DATEDIFF(DAY, dt_start, dt_end) + 1) 
                     ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
                 FROM sys.all_columns
             ) AS nums
         ) AS all_days
         WHERE DATENAME(WEEKDAY, work_day) NOT IN ('Saturday', 'Sunday')
        ) AS total_work_days,
        -- 提取起始日距离当天0点的秒数
        DATEDIFF(SECOND, CAST(dt_start AS DATE), dt_start) AS start_time_sec,
        -- 提取结束日距离当天0点的秒数
        DATEDIFF(SECOND, CAST(dt_end AS DATE), dt_end) AS end_time_sec
    FROM cte
),
total_seconds AS (
    SELECT
        start_datetime,
        end_datetime,
        time_sign * 
        CASE 
            -- 无工作日时时间差为0
            WHEN total_work_days = 0 THEN 0
            -- 仅同一天且为工作日，直接计算当天时间差
            WHEN total_work_days = 1 THEN end_time_sec - start_time_sec
            -- 跨多个工作日：中间完整工作日秒数 + 起始日剩余时间 + 结束日已过时间
            ELSE (total_work_days - 2) * 86400 + (86400 - start_time_sec) + end_time_sec
        END AS total_work_sec
    FROM workday_count
)
SELECT
    start_datetime,
    end_datetime,
    -- 转换为目标格式：'days, hh:mm:ss'，自动处理正负号
    CONCAT(
        CASE WHEN total_work_sec < 0 THEN '-' ELSE '' END,
        ABS(total_work_sec) / 86400, ', ',
        FORMAT(CAST((ABS(total_work_sec) % 86400) / 3600 AS INT), '00'), ':',
        FORMAT(CAST((ABS(total_work_sec) % 3600) / 60 AS INT), '00'), ':',
        FORMAT(ABS(total_work_sec) % 60, '00')
    ) AS work_duration,
    -- 拆分输出时间组件（替代原生duration类型，方便后续计算）
    ABS(total_work_sec) / 86400 AS work_days,
    CAST((ABS(total_work_sec) % 86400) / 3600 AS INT) AS work_hours,
    CAST((ABS(total_work_sec) % 3600) / 60 AS INT) AS work_minutes,
    ABS(total_work_sec) % 60 AS work_seconds,
    CASE WHEN total_work_sec < 0 THEN -1 ELSE 1 END AS duration_sign
FROM total_seconds;

-- 清理临时表
DROP TABLE #temptable;

关键逻辑说明 #

1. 统一正负处理：通过cte将时间差转为绝对值计算，最后用time_sign还原正负方向，避免正负分支逻辑重复。
2. 工作日统计：生成时间范围内的所有日期，筛选出周一至周五的日期并计数。
3. 时分秒精确计算：
   • 同一天工作日：直接计算当天时间差
   • 跨多天工作日：计算中间完整工作日的总时长，加上起始日剩余时间、结束日已过时间
4. 格式转换：将总秒数转为days, hh:mm:ss格式，自动补零并添加正负号。

关于Duration类型说明 #

SQL Server 2019无原生duration数据类型，可通过拆分的work_days/work_hours/work_minutes/work_seconds字段组合模拟，或直接使用work_duration字符串格式作为最终输出。

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