Como convertir las columnas run_date y run_time de job_history a datetime en SQL Server

Hola, chicos,
Buen día.

Hoy le mostraré rápidamente cómo convertir las columnas run_date y run_time de la tabla del catálogo de la base de datos msdb.dbo.sysjobhistory a datetime. Actualmente, la columna run_date es un varchar en el formato aaaammdd (por ejemplo: 05/07/2015 = 20150507) y la columna run_time es una hora en el formato hmmss (por ejemplo: 08:27:00 = 82700). Incluso puedes entender visualmente qué significan estos valores, pero el cálculo con estas fechas y horas es mucho más complicado.

Para facilitar la consulta de esta información, podemos combinar estas 2 columnas y convertirlas a fecha y hora. Demostraré dos formas de hacer esto:

Usando la función del 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

Creando una función personalizada

Aunque ya existe una función del sistema para hacer esto (Gracias a Carolina Goltara, como consejo), a muchos administradores de bases de datos no les gusta conceder acceso a las funciones del sistema a los analistas, aunque sepan lo que hace la función. Para solucionar esto, podemos crear una función personalizada y liberar el acceso a esta función para los 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

Y ahora, usemos la función creada para mostrar los 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

El resultado final es exactamente el mismo usando ambas funciones, parecido a la imagen a continuación: