Have you ever queried the msdb tables to get duration details about a particular job? I’ve had to do this many times in the past, but never blogged about it and when I needed it recently, I couldn’t find my scripts. I could have done a quick search for what others have done, but I thought I would deep into my brain housing group and see what I can dig up! At any rate, the duration columns often are returned as integer values. The values are not in milliseconds or even seconds and it is not very simple to convert them that way. Let me show you a quick sample query so that you see what I mean.

Script 1: Quick Query to See Run Duration

SELECT run_duration FROM msdb..sysjobhistory

Figure 1: Results

Looking at the results above, what the run duration is saying for record 1 is that it took 10 minutes and 9 seconds. Record 2 is 6 hours, 12 minutes and 55 seconds. As you can see, it would take quite a bit ofwork to get this into seconds. So, what I’ve done below is give you one method of getting duration into a little easier to read format.

Script 2: My Custom Run Duration

SELECT

h.run_duration

,CASELEN(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))