;

WITH MScte

AS (

SELECT h.server,

h.job_id,

h.run_duration,

h.run_date,

h.run_time,

CASE LEN(h.run_duration) WHEN 1 THEN '00:00:0' + CONVERT(CHAR(1), h.run_duration)

WHEN 2 THEN '00:00:' + CONVERT(CHAR(2), h.run_duration)

WHEN 3 THEN '00:0' + CONVERT(CHAR(1), LEFT(h.run_duration, 1)) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2)) WHEN 4 THEN '00:' + CONVERT(CHAR(2), LEFT(h.run_duration, 2)) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2))

WHEN 5 THEN '0' + CONVERT(CHAR(1), LEFT(h.run_duration, 1)) + ':' + LEFT(RIGHT(h.run_duration, 4), 2) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2))

ELSE CONVERT(VARCHAR(4), LEFT(h.run_duration, LEN(h.run_duration) - 4)) + ':' + LEFT(RIGHT(h.run_duration, 4), 2) + ':' + CONVERT(CHAR(2), RIGHT(h.run_duration, 2))

END AS My_Custom_Run_Duration

FROM msdb..sysjobhistory h

)

SELECT h.SERVER

,j.NAME

,h.run_date

,h.run_time

,h.run_duration

FROM msdb.dbo.sysjobs j

inner join MScte h

--INNER JOIN msdb.dbo.sysjobhistory h

ON h.job_id = j.job_id

ORDER BY j.NAME;