How to convert the run_date and run_time columns from job_history to datetime in SQL Server

Hey guys,
Good morning.

Today I'm going to quickly show you how to convert the run_date and run_time columns from the msdb.dbo.sysjobhistory database catalog table to datetime. Currently, the run_date column is a varchar in the format yyyymmdd (Ex: 07/05/2015 = 20150507), and the run_time column is a time in the format hmmss (Ex: 08:27:00 = 82700). You can even visually understand what these values ​​mean, but the calculation with these dates and times is much more complicated.

To make it easier to query this information, we can combine these 2 columns and convert them to datetime. I will demonstrate two ways on how to do this:

Using the msdb.dbo.agent_datetime system function

SELECT
    j.name,
    h.step_id,
    h.step_name,
    h.run_status,
    h.message,
    [RunDateTime] = msdb.dbo.agent_datetime(h.run_date, h.run_time),
    h.run_date,
    h.run_time
FROM
    [msdb].[dbo].[sysjobs] j
    JOIN [msdb].[dbo].sysjobhistory h ON j.job_id = h.job_id
WHERE
    h.run_status = 0 AND h.step_id = 0

Creating a custom function

Although there is already a system function to do this (Thanks to Caroline Goltara, for the tip), many DBA’s do not like to grant access to system functions to analysts, even though they know what the function does. To get around this, we can create a custom role and release access to this role for analysts.

USE [Util]
GO
 
CREATE FUNCTION [dbo].[fncJobs_Converte_Datetime] (
    @DATE INT,
    @TIME INT
)
RETURNS datetime
AS BEGIN
 
    DECLARE @Date_Time datetime
 
    DECLARE @Ds_Date VARCHAR(8) = @DATE
    DECLARE @Ds_Time VARCHAR(8) = @TIME
 
    IF (@DATE = 0) RETURN NULL
 
    SET @Ds_Time = RIGHT('000000'+@Ds_Time,6)
    SET @Ds_Time = SUBSTRING(@Ds_Time,1,2)+':'+SUBSTRING(@Ds_Time,3,2)+':'+SUBSTRING(@Ds_Time,5,2)
 
    SET @Date_Time = CAST(@Ds_Date + ' ' + @Ds_Time AS datetime)
 
    RETURN @Date_Time	
END

And now, let's use the created function to display the results:

SELECT
    j.name,
    h.step_id,
    h.step_name,
    h.run_status,
    h.message,
    [RunDateTime] = Util.dbo.fncJobs_Converte_Datetime(h.run_date, h.run_time)
FROM
    [msdb].[dbo].[sysjobs] j
    JOIN [msdb].[dbo].sysjobhistory h ON j.job_id = h.job_id
WHERE
    h.run_status = 0 
    AND h.step_id = 0

The end result is exactly the same using both functions, looking like the image below: