There are times when you may want to query the dbo.sysjobhistory table in msdb for reporting and diagnostics of jobs. You may notice that the run_time and run_duration is in an int
. To convert from int
to a readable format, use the following T-SQL code:
DECLARE @runTime AS INT = 235900
DECLARE @runDuration AS INT = 5817
SELECT STUFF(STUFF(RIGHT(REPLICATE('0', 6) + CAST(@runTime as varchar(6)), 6), 3, 0, ':'), 6, 0, ':') 'run_time',
STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(@runDuration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') 'run_duration (DD:HH:MM:SS) '
In this example, the run time will display as 23:59:00 or 11:59PM and the run duration will display as 00:00:58:17 or 58 minutes and 17 seconds.