Someone asked the following question on our forum: HHMMSS format in sysjobhistory.
The sysjobhistory table in MSDB stores the run time of jobs in the format HHMMSS as an integer. Thus, a job that finishes in 18 minutes and 9 seconds is stored as 1809. If a job finishes in 4 seconds it’s stored as 4, and another that finishes in 7 hours, 21 minutes, 33 seconds as 72133. I’m trying to get to this data and need it formated in the time data type. Thus I’m looking for 00:18:09, 00:00:04, and 07:21:33 respectively.
This is pretty simple to do in a 2 step process
Step 1 is to make sure that you always have 6 characters, you can do that by using the right functions and padding the total characters to 6, here is an example
RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6 )
Once you have that, you can use the STUFF function to inject colons in position 3 and 6, the code would look like this
The 0 means that you don’t want to replace any characters
So to put it all together, you can use a CTE (Common Table Expression) combined with the stuff function to accomplish this job
Here is all the code that is needed
;WITH cte AS(SELECT RIGHT('000000' + CONVERT(VARCHAR(6),run_duration),6 ) AS FormattedTime,* FROM msdb..sysjobhistory WHERE run_duration >=0) SELECT STUFF(STUFF(FormattedTime,3,0,':'),6,0,':'),* FROM cte
Now I don’t have any jobs that run over 24 hours, I might have some of them that are 16 hours or so I am not sure if it adds to the hours or if it starts to add 2 more digits for the days
If you have never used the STUFF function before make sure to read our wiki: Ten SQL Server Functions That You Have Ignored Until Now to learn about other seldom used functions like