[译] 所有SQL服务器工作的最后持续时间，以TIME格式从HHmmss到HH:mm:ss

原文地址：SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss
原文作者：Stéphane Haby

对于客户，我想查看作业的所有最后持续时间并设置良好的格式。
其实一开始执行此操作的查询非常简单，但是当我尝试获得更好的视图时，它变得更加复杂。

让我尝试来解释下。

就像您现在一样，要拥有 SQL Server 上所有作业的历史记录，我们使用系统视图 dbo.sysjobhistory 和 dbo.sysjobs

我使用此查询来获取最后日期和运行持续时间：

USE msdb
SELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration
FROM sysjobhistory jh, sysjobs j
WHERE jh.job_id = j.job_id AND j.Name like 'DM_%'  AND run_status = 1
AND jh.run_date = 
(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)
ORDER BY jh.run_duration desc
查询和维护作业的一个小例子：

运行时间是 INT 作为数据类型，格式为 HHmmss。

这意味着对于作业 DBIntegrityCheck – 在我的示例中的 USER_DBs，我们有 5749 代表 57 分 49 秒。
我想将其转换为时间格式以获得更好的视图并具有 HH:mm:ss 而不是 HHmmss。
我尝试使用 CAST 和 CONVERT 并首先转换为 varchar：
SELECT CAST (5749 as TIME)
SELECT CAST ( CAST(5749 as VARCHAR) as TIME)
SELECT CONVERT (TIME, 5749)
SELECT CONVERT (TIME,CONVERT (VARCHAR, 5749))
简单的 CAST 和 CONVERT to TIME 不适用于错误：
不允许从数据类型 int 到 time 的显式转换。
CAST 和 CONVERT to TIME 首先转换为 varchar 有效，但结果不好：

我能做些什么？
我将使用 msdb.dbo.agent_datetime(YYYYmmdd, HHmmss) 来转换它，因为它正是我当时需要的格式。我使用 19000101 作为默认日期并为 TIME 计算成本：
SELECT MSDB.DBO.AGENT_DATETIME(19000101,5749)
SELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,5749) as time)
运行查询…

这确实是我搜索的结果。
现在，我在查询中通过 msdb.dbo.agent_datetime 进行此转换：
USE msdb
SELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration,
cast (MSDB.DBO.AGENT_DATETIME(jh.run_duration,5749) as time) as run_duration_time
FROM sysjobhistory jh, sysjobs j
WHERE jh.job_id = j.job_id AND run_status = 1
AND jh.run_date = 
(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)
ORDER BY jh.run_duration desc
运行查询后，我收到一条错误消息：
消息 241，级别 16，状态 1，第 3 行

从字符串转换日期和/或时间时转换失败。
为什么会出现这个错误…

根据第一张截图的结果，更大的时间是 311342，超过了 24 小时！按照此处

的文档，时间格式限制在 00:00:00.0000000 到 23:59:59.9999999 之间
如果我们单独运行查询：
SELECT MSDB.DBO.AGENT_DATETIME(19000101,311342)
SELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,311342) as time)
如果我在 23:59:59 运行查询，它正在工作：
SELECT MSDB.DBO.AGENT_DATETIME(19000101,235959)
SELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,235959) as time)
再次，不可能转换上 240000 …

使用 msdb.dbo.agent_datetime 绝对不是我需要的好主意。

下一步是通过varchar将每个数字以时间单位转换并格式化。
我把这个时间作为我的价值案例并转换为时间单位：
select (311342 / 10000) % 100 as hour,
       (311342 / 100) % 100 as minute,
       (311342 / 10) % 100 as second
作为解决方法，我尝试将其转换为时间：
select dateadd(hour, (311342 / 10000) % 100,
       dateadd(minute, (311342 / 100) % 100,
       dateadd(second, (311342) % 100,
       dateadd(millisecond, 0, cast('00:00:00' as time)))))
结果是 07:13:42 而不是预期的 31:13:42……
最后，要获得我将使用的时间格式的解决方案，我需要使用格式 varchar(8)：
select Cast ((311342 / 10000) % 100 as varchar) + ':' +
       Cast ((311342 / 100) % 100 as varchar) + ':' +
       Cast ((311342) % 100 as varchar)
我将它插入到查询中以获得每个作业的持续时间：
USE msdb
SELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration,
Cast ((jh.run_duration / 10000) % 100 as varchar) + ':' +
       Cast ((jh.run_duration / 100) % 100 as varchar) + ':' +
       Cast ((jh.run_duration) % 100 as varchar) as run_duration_time
FROM sysjobhistory jh, sysjobs j
WHERE jh.job_id = j.job_id AND run_status = 1 
AND jh.run_date = 
(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)
ORDER BY jh.run_duration desc