Format sysjobhistory datetime & duration Columns in SQL Server

If you’ve ever queried the sysjobhistory table in the msdb database, you’ll probably know that the datetime and duration columns are stored as integers.

In particular, when you query this table, the run_date, run_time, and duration columns are returned as integers, which can make it difficult to read.

Below is a query you can use to return this data in an easier to read format.

The Problem

First, let’s look at how these columns are returned:

SELECT TOP 15
	run_date,
	run_time,
	run_duration
FROM msdb.dbo.sysjobhistory
ORDER BY run_duration DESC;

Result:

run_date    run_time    run_duration
 
 20201215    155451      625
 20201215    155948      128
 20201115    20001       17
 20201115    20002       16
 20201218    44026       13
 20201217    20000       10
 20201218    44029       10
 20201216    21902       9
 20201217    20001       9
 20201219    23527       9
 20201213    34249       8
 20201216    21903       8
 20201219    23528       8
 20201213    34250       7
 20201214    32114       7

Here’s how each column is stored/presented:

The run_date column is stored in YYYYMMDD format.
The run_time column is stored in HHMMSS format on a 24-hour clock. But there are no leading zeros.
The run_duration column is stored in HHMMSS format. Again, there are no leading zeros. Plus there are no colons to help us distinguish between each segment. So in the above example, the first job finished in 6 minutes and 25 seconds, the second job finished in 1 minute and 28 seconds, and the third job finished in 17 seconds.

Once you know how the formatting works, it’s usually not too difficult to figure it out. But it can be unintuitive for us humans to read.

The Solution

Here’s a solution that presents the data in a more human-readable format:

SELECT TOP 15
	msdb.dbo.AGENT_DATETIME(run_date, run_time) AS RunDateTime,
	STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') AS RunDuration
FROM msdb.dbo.sysjobhistory
ORDER BY run_duration DESC;

Result:

RunDateTime                RunDuration
 
 2020-12-15 15:54:51.000    00:00:06:25
 2020-12-15 15:59:48.000    00:00:01:28
 2020-11-15 02:00:01.000    00:00:00:17
 2020-11-15 02:00:02.000    00:00:00:16
 2020-12-18 04:40:26.000    00:00:00:13
 2020-12-17 02:00:00.000    00:00:00:10
 2020-12-18 04:40:29.000    00:00:00:10
 2020-12-16 02:19:02.000    00:00:00:09
 2020-12-17 02:00:01.000    00:00:00:09
 2020-12-19 02:35:27.000    00:00:00:09
 2020-12-13 03:42:49.000    00:00:00:08
 2020-12-16 02:19:03.000    00:00:00:08
 2020-12-19 02:35:28.000    00:00:00:08
 2020-12-13 03:42:50.000    00:00:00:07
 2020-12-14 03:21:14.000    00:00:00:07

Here, I use the undocumented AGENT_DATETIME() function to convert the run_date and run_time columns into a more readable format.

I then use a series of T-SQL functions (STUFF(), RIGHT(), CAST(), and REPLICATE()) to get the run_duration column into a more human-readable format. These ensure that there are colons inserted at a suitable place, and that there are always two digits (including a leading zero if needed) for each segment.

More Formatting

You could take it a step further and use other functions, such as the FORMAT() function to present the run_date and run_time columns in a format that’s even more reader-friendly.

SELECT TOP 15
	FORMAT(msdb.dbo.AGENT_DATETIME(run_date, run_time), 'U') AS RunDateTime
FROM msdb.dbo.sysjobhistory
ORDER BY run_duration DESC;

Result:

RunDateTime
 Tuesday, December 15, 2020 11:54:51 PM
 Tuesday, December 15, 2020 11:59:48 PM
 Sunday, November 15, 2020 10:00:01 AM
 Sunday, November 15, 2020 10:00:02 AM
 Friday, December 18, 2020 12:40:26 PM
 Thursday, December 17, 2020 10:00:00 AM
 Friday, December 18, 2020 12:40:29 PM
 Wednesday, December 16, 2020 10:19:02 AM
 Thursday, December 17, 2020 10:00:01 AM
 Saturday, December 19, 2020 10:35:27 AM
 Sunday, December 13, 2020 11:42:49 AM
 Wednesday, December 16, 2020 10:19:03 AM
 Saturday, December 19, 2020 10:35:28 AM
 Sunday, December 13, 2020 11:42:50 AM
 Monday, December 14, 2020 11:21:14 AM

You can use any number of other format specifiers with this function, as well as a culture argument.

For more information and examples, see:

Getting the Job Name

The sysjobhistory table doesn’t store job names. It only stores their IDs.

To return the job name along with the date/time/duration data, you can perform a join on the sysjobs_view view (or the sysjobs table) to get the job name.

Here’s an example of a query that does that:

SELECT jv.name AS Job,
		jh.step_name AS Step,
		msdb.dbo.AGENT_DATETIME(jh.run_date, jh.run_time) AS RunDateTime,
		STUFF(STUFF(STUFF(RIGHT(REPLICATE('0', 8) + CAST(jh.run_duration as varchar(8)), 8), 3, 0, ':'), 6, 0, ':'), 9, 0, ':') AS RunDuration
FROM msdb.dbo.sysjobs_view jv
INNER JOIN msdb.dbo.sysjobhistory jh
ON jv.job_id = jh.job_id
ORDER BY Job, RunDateTime;

Result: