[master_admin].[dbo].[fn_INTtoDateTime]

I use this when querying msdb.dbo.sysjobhistory.

USE [master_admin]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_INTtoDateTime] (
@vcDate VARCHAR(8),
@vcTime VARCHAR(6)
)
RETURNS DATETIME
AS
BEGIN
SET @vcDate = RIGHT(RTRIM('00000000' + CONVERT(CHAR(8), @vcDate) + ' '), 8)
SET @vcTime = RIGHT(RTRIM('00000000' + CONVERT(CHAR(6), @vcTime) + ' '), 6)

IF @vcDate = '00000000'
SET @vcDate = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(23), CAST(0 AS SMALLDATETIME), 121),
'-', ''), ':', ''),
SPACE(1), ''), 8)

RETURN (
CAST(SUBSTRING(@vcDate,1,4) + '-' + SUBSTRING(@vcDate,5,2) + '-' + SUBSTRING(@vcDate,7,2) + ' ' +
SUBSTRING(@vcTime,1,2) + ':' + SUBSTRING(@vcTime,3,2) + ':' + SUBSTRING(@vcTime,5,2)
AS DATETIME)
)
END

GO