sysjobhistory run_duration Conversion

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.

 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:

 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.jobscatter


2 thoughts on “sysjobhistory run_duration Conversion

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s