修复SQL Server作业完成时间计算查询：处理run_duration超24小时场景

msdb.dbo.sysjobhistory 存储了作业的开始时间，以及用于计算结束时间的run_duration整数（格式为HHMMSS，超过24小时时会直接显示如250910，代表25小时9分10秒）。

原查询尝试将运行时长转为datetime后与开始时间相加，但当run_duration超过24小时时，转换会失败：

SELECT CONVERT(datetime, msdb.dbo.agent_datetime(run_date, run_time))+
        CONVERT(datetime, STUFF(STUFF(RIGHT('000000' + CAST ( run_duration AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') ) As JobFinishTime
 FROM  msdb.dbo.sysjobs j  
 INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id  
 where h.step_id = 0 AND h.run_status=1 AND j.enabled = 1  
 AND  j.enabled = 1

比如当run_duration为250910时，CONVERT(datetime, '25:09:10')不符合datetime的时间范围限制，直接导致查询报错。

修复后的查询 #

正确做法是将run_duration拆解为小时、分钟、秒，通过DATEADD函数逐步累加，避开datetime的转换限制：

SELECT 
    DATEADD(SECOND, 
        (run_duration % 100) + -- 提取秒数
        ((run_duration / 100) % 100) * 60 + -- 提取分钟数并转为秒
        (run_duration / 10000) * 3600, -- 提取小时数并转为秒
        msdb.dbo.agent_datetime(run_date, run_time)
    ) AS JobFinishTime
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id
WHERE h.step_id = 0 
  AND h.run_status = 1 
  AND j.enabled = 1

验证示例 #

用给定的测试值验证修复效果：

SELECT 
    DATEADD(SECOND, 
        (250910 % 100) + 
        ((250910 / 100) % 100) * 60 + 
        (250910 / 10000) * 3600, 
        msdb.dbo.agent_datetime(20230411, 70358)
    ) AS JobFinishTime

该查询会正确返回2023-04-12 08:13:08（即2023-04-11 07:03:58加上25小时9分10秒后的结果）。

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