Como converter as colunas run_date e run_time da job_history para datetime no SQL Server

Olá Pessoal,
Bom dia.

Hoje vou mostrar rapidamente pra vocês, como converter as colunas run_date e run_time da tabela de catálogo do banco msdb.dbo.sysjobhistory para datetime. Atualmente, a coluna run_date é um varchar no formato yyyymmdd (Ex: 07/05/2015 = 20150507), e a coluna run_time é uma hora no formato hmmss (Ex: 08:27:00 = 82700). Até dá pra entender visualmente o que significam esses valores, mas o cálculo com essas datas e horas ficam bem mais complicado.

Para facilitar a consulta dessas informações, podemos combinar essas 2 colunas e convertê-las para datetime. Vou demonstrar duas formas de como fazer isso:

Utilizando a função de sistema msdb.dbo.agent_datetime

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

Criando uma função personalizada

Apesar de já existir uma função de sistema para fazer isso (Agradecimentos a Caroline Goltara, pela dica), muitos DBA’s não gostam de conceder acesso a funções de sistema para analistas, mesmo sabendo o que função faz. Para contornar nisso, podemos criar uma função personalizada e liberar acesso nessa função para os analistas.

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

E agora, vamos utilizar a função criada para exibir os resultados:

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

O resultado final é exatamente o mesmo utilizando as duas funções, ficando como na imagem abaixo: