System Function msdb.dbo.agent_datetimeMSDB.dbo.sysjobhistory Table and Run_duration ColumnPosted: February 15, 2016
In the msdb.dbo.sysjobhistory table, the run_duration column is INT type and stored as (H)HHMMSS format from right to left.
If HH is under 24 hours, we can get the during in time format HH:MM:SS by using a system function. For example:
select msdb.dbo.agent_datetime(19000101, 231353) will return 23:13:53 9 23 hours 13 minutes and 53 seconds.
In very long running job, if the hours go over 24 hours, all the numbers in HHH positions are whole number of hours.
We need to tweak a little to make it work:
SELECT j.NAME AS 'Job', run_date, run_time, msdb.dbo.Agent_datetime(run_date, run_time) [Run_Datetime] ,Case when run_duration>24*10000 then Cast(run_duration%240000/10000 as varchar(3)) +' (day) ' + Cast(run_duration/240000 as varchar(2)) +':' + LefT(Right(Cast(run_duration as varchar(8)),4),2) +':'+ Right(Cast(run_duration as varchar(8)),2) +'' else Format(msdb.dbo.agent_datetime(19000101, run_duration) ,'HH:mm:ss') end [Duriation in HH:MM:SS] FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobhistory h ON j.job_id = h.job_id WHERE j.enabled = 1 ORDER BY run_duration DESC