Below is a quick script to convert the run_duration column into 3 separate columns for Hours, Minutes, and seconds. The script will also convert the run_date column to a date. Useful for dropping into Excel and running reports.
SELECT runDate = CONVERT (DATETIME, RTRIM(run_date)), run_duration/10000 Hours, --hours run_duration/100%100 Minutes, --minutes run_duration%100 Seconds --seconds from sysJobHistory
Just add a job_id in your where clause if you want to track a single job. I used this script to generate a report of run times for a specific job. We had a SQL job run long (so we thought) and wanted to drill into the numbers a bit. After some investigation we discovered that the job was pretty consistent and that the long run time was consistent with the job history.
You can also just combine the hours, minutes, and seconds into 1 column like so:
SELECT runDate = CONVERT (DATETIME, RTRIM(run_date)),CAST(run_duration/10000 as varchar) + ':' + CAST(run_duration/100%100 as varchar) + ':' + CAST(run_duration%100 as varchar) from sysJobHistory
From there you can paste the data into Excel and chart it out or do whatever else you want to do with that data.
It appears your calculation of run_duration doesn’t work when the last digits are larger than 60.
Try the following instead, CASE
–hours, minutes and seconds
WHEN LEN(run_duration) > 4 THEN CONVERT(VARCHAR(4),LEFT(run_duration,LEN(run_duration)-4)) * 3600
+ LEFT(RIGHT(run_duration,4),2) * 60 + RIGHT(run_duration,2)
–minutes and seconds
WHEN LEN(run_duration) = 4 THEN LEFT(run_duration,2) * 60 + RIGHT(run_duration,2)
WHEN LEN(run_duration) = 3 THEN LEFT(run_duration,1) * 60 + RIGHT(run_duration,2)
ELSE –only seconds
RIGHT(run_duration,2)
END
The math for the conversion to hours, minutes and seconds was perfect. Nice!