SQL Server: Last duration for all SQL Server jobs in TIME format from HHmmss to HH:mm:ss

For a customer, I want to see all last duration for a job and have a good format for it.
The query to do it is very simple (at the beginning…) but when I try to have a better view it was more complicated.

Let me try to explain you.

As you now, to have the history of all jobs on SQL Server, we use system views dbo.sysjobhistory and dbo.sysjobs

I use this query to have the last date and run duration:

USE msdb
GO
SELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration
FROM sysjobhistory jh, sysjobs j
WHERE jh.job_id = j.job_id AND j.Name like 'DM_%'  AND run_status = 1
AND jh.run_date = 
(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)
ORDER BY jh.run_duration desc
GO

A little example with the query and maintenance jobs:

The run time is an INT as data type where the format is HHmmss.
This mean for the job DBIntegrityCheck – USER_DBs in my example, we have 5749 representing 57 minutes and 49 seconds.

I thinking to convert it in a time format to have a better view and have HH:mm:ss and not HHmmss.

I try with CAST and CONVERT and to convert also first to varchar:

SELECT CAST (5749 as TIME)
GO
SELECT CAST ( CAST(5749 as VARCHAR) as TIME)
GO
SELECT CONVERT (TIME, 5749)
GO
SELECT CONVERT (TIME,CONVERT (VARCHAR, 5749))
GO

The simple CAST and CONVERT to TIME does not working with the error:

Explicit conversion from data type int to time is not allowed.

The CAST and CONVERT to TIME with first a conversion to varchar works but the result is not good:

What can I do?

I will use msdb.dbo.agent_datetime(YYYYmmdd, HHmmss) to convert it because it’s exactly the format I need for the time. I use 19000101 as default date and do a cost for the TIME:

SELECT MSDB.DBO.AGENT_DATETIME(19000101,5749)
SELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,5749) as time)

Running the query…

It’s really the result that I search to have.

Now I put this conversion through msdb.dbo.agent_datetime in my query:

USE msdb
GO
SELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration,
cast (MSDB.DBO.AGENT_DATETIME(jh.run_duration,5749) as time) as run_duration_time
FROM sysjobhistory jh, sysjobs j
WHERE jh.job_id = j.job_id AND run_status = 1
AND jh.run_date = 
(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)
ORDER BY jh.run_duration desc
GO

After running the query, I receive an error message:

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

Why this error…
After regarding the result on the first screenshot, the bigger time is 311342 and it’s more than 24hours!
The format TIME is limited from 00:00:00.0000000 through 23:59:59.9999999 folowing the documentation here 

If we run the query separately:

SELECT MSDB.DBO.AGENT_DATETIME(19000101,311342)
SELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,311342) as time)

If I run the query with 23:59:59, it’s working:

SELECT MSDB.DBO.AGENT_DATETIME(19000101,235959)
SELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,235959) as time)

If I run the query with 24:00:0, it’s not working:

SELECT MSDB.DBO.AGENT_DATETIME(19000101,240000)
SELECT cast (MSDB.DBO.AGENT_DATETIME(19000101,240000) as time)

Again it’s not possible to convert upper 240000…
Using msdb.dbo.agent_datetime is definitively not the good idea for what I need.
The next step is to convert each number in time unit through varchar and to format it.

I take this time my worth case and convert into time unit:

select (311342 / 10000) % 100 as hour,
       (311342 / 100) % 100 as minute,
       (311342 / 10) % 100 as second

As workaround, I try to transform it to Time:

select dateadd(hour, (311342 / 10000) % 100,
       dateadd(minute, (311342 / 100) % 100,
       dateadd(second, (311342) % 100,
       dateadd(millisecond, 0, cast('00:00:00' as time)))))

And the result is 07:13:42 and not like expected 31:13:42…

Finally to have a solution in the Time format that I will, I need to use the format varchar(8):

select Cast ((311342 / 10000) % 100 as varchar) + ':' +
       Cast ((311342 / 100) % 100 as varchar) + ':' +
       Cast ((311342) % 100 as varchar)

I insert it in the query to have the duration for each job:

USE msdb
GO
SELECT DISTINCT j.Name AS job_name,jh.run_date AS last_run_date, jh.run_duration as run_duration,
Cast ((jh.run_duration / 10000) % 100 as varchar) + ':' +
       Cast ((jh.run_duration / 100) % 100 as varchar) + ':' +
       Cast ((jh.run_duration) % 100 as varchar) as run_duration_time
FROM sysjobhistory jh, sysjobs j
WHERE jh.job_id = j.job_id AND run_status = 1 
AND jh.run_date = 
(SELECT MAX(jhm.run_date) FROM sysjobhistory jhm WHERE jh.job_id = jhm.job_id)
ORDER BY jh.run_duration desc
GO

Now as you can see, the format of the duration is like I wish at the beginning with a lot test and improvement…
I hope this script and steps can help you to understand conversion to TIME data type.