Display Job History

We have a service that polls a myriad of things several times an hour to send pages and alerts to the appropriate people based on a specific set of criteria. One of the things that it polls is the SQL job history, which can be helpful to be proactive in catching long running jobs or even if a job gets disabled.

The time that a job is scheduled to run and it’s run duration is saved as an integer in hhmmss format, so converting this to the datetime datatype requires some manipulation. You can find a lot of ways to do this using string functions, which is the way we had completed the conversion … until is started erroring.

The statement below will convert the Integer value to the datetime datatype using mathmatical functions instead of string functions, which is less prone to error.

The first thing that we will do is extract the hours, minutes and seconds into their separate parts using the modulo operator. Once we have that we will convert these values into seconds (the smallest interval we are working with) and add them together. Once we have the offset value in seconds from midnight we use the dateadd function and add the seconds to the run date. Viola! And it’s even a little faster.

SELECT
    DATEADD(ss,(((Run_Time - Run_Time % 10000) / 1e4) * 36e2 + 
                ((Run_Time % 10000 - Run_Time % 100) / 1e2) * 6e1 + 
                  Run_Time % 100),CAST(CAST(Run_Date AS char(8)) AS datetime)) StartTime,
    DATEADD(ss,(((Run_Time - Run_Time % 10000) / 1e4) * 36e2 + 
                ((Run_Time % 10000 - Run_Time % 100) / 1e2) * 6e1 + 
                  Run_Time % 100 + 
                ((Run_Duration - Run_Duration % 10000) / 1e4) * 36e2 + 
                ((Run_Duration % 10000 - Run_Duration % 100) / 1e2) * 6e1 + 
                  Run_Duration % 100),CAST(CAST(Run_Date AS char(8)) AS datetime)) EndTime,
    j.name JobName,
    jh.step_name StepName
FROM
    msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
    ON jh.job_id = j.job_id
WHERE
    jh.step_id > 0
ORDER BY
    j.job_id,
    jh.step_id ;