Turning sysjobhistory's integer dates, times and durations into something readable

#The format

Jobs running more than 99 hours show durations like 1023015 (102:30:15).

#Start time as datetime

SELECT DATETIMEFROMPARTS(
         h.run_date / 10000, h.run_date / 100 % 100, h.run_date % 100,
         h.run_time / 10000, h.run_time / 100 % 100, h.run_time % 100, 0) AS run_start
FROM msdb.dbo.sysjobhistory h;

msdb also has dbo.agent_datetime(run_date, run_time). It's undocumented but has been there for years. The arithmetic version above is explicit and fast.

#Duration in seconds

SELECT (h.run_duration / 10000) * 3600
     + (h.run_duration / 100 % 100) * 60
     +  h.run_duration % 100 AS duration_sec
FROM msdb.dbo.sysjobhistory h;

#Duration as hh:mm:ss text

SELECT RIGHT('00' + CAST(h.run_duration / 10000 AS varchar(5)), 2) + ':' +
       RIGHT('0'  + CAST(h.run_duration / 100 % 100 AS varchar(2)), 2) + ':' +
       RIGHT('0'  + CAST(h.run_duration % 100 AS varchar(2)), 2) AS duration_hms
FROM msdb.dbo.sysjobhistory h;

#End time

SELECT DATEADD(SECOND,
         (h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100,
         DATETIMEFROMPARTS(h.run_date / 10000, h.run_date / 100 % 100, h.run_date % 100,
                           h.run_time / 10000, h.run_time / 100 % 100, h.run_time % 100, 0)) AS run_end
FROM msdb.dbo.sysjobhistory h;

#A reusable view

Create it in a DBA utility database, not msdb, so upgrades don't touch it:

CREATE OR ALTER VIEW dbo.v_AgentHistory AS
SELECT j.name AS job_name,
       h.step_id, h.step_name,
       CASE h.run_status WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry'
                         WHEN 3 THEN 'Cancelled' WHEN 4 THEN 'In progress' END AS status,
       x.run_start,
       x.duration_sec,
       DATEADD(SECOND, x.duration_sec, x.run_start) AS run_end,
       h.message, h.instance_id
FROM msdb.dbo.sysjobhistory h
JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id
CROSS APPLY (SELECT
   DATETIMEFROMPARTS(h.run_date / 10000, h.run_date / 100 % 100, h.run_date % 100,
                     h.run_time / 10000, h.run_time / 100 % 100, h.run_time % 100, 0) AS run_start,
   (h.run_duration / 10000) * 3600 + (h.run_duration / 100 % 100) * 60 + h.run_duration % 100 AS duration_sec) x;

Example use: the slowest runs of the last week.

SELECT TOP (20) job_name, run_start, duration_sec / 60.0 AS minutes, status
FROM dbo.v_AgentHistory
WHERE step_id = 0 AND run_start >= DATEADD(DAY, -7, SYSDATETIME())
ORDER BY duration_sec DESC;

#Gotchas

• step_id = 0 is the job outcome row; steps are 1..n.
• A job in progress has no outcome row yet. Use sysjobactivity for running jobs.
• run_date/run_time are server local time. Convert with AT TIME ZONE if you combine data from servers in several zones.

Tip

Don't sum run_duration directly. 130 + 130 = 260 looks like "2 min 60 s". Convert to seconds first.