Convert MSDB Date and Time INTEGERS to DATETIME

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 4 May 2010

Description

SQL Agent job reporting via SQL Server Management studio is at best mediocre. It also requires installation of Management Studio for anyone who wants to view the job status. Nothing strange or unexpected with that - this kind of thing is prevalent with all major pieces of software, and nobody can reasonably expect all reporting requirements to be covered by any piece of software. This is usually the most common kind of coding that a DBA gets involved in - ie automation or extension of management tools functionality.

However, MSDB SQL Agent tables are like something out of the dark ages when it comes to storing dates and times. Disappointing that these structures have remained virtually unchanged in the last 17 years that I've been using them, but then a scheduling agent is hardly the most glamorous, visible or marketable portion of any software and thus doesn't usually get the attention it requires.

MSDB stored dates and times as 2 separate integers, with no leading zeros to be helpful:

20111111 154052 = 11 Nov 2011 15:40:52

20111222 90730 = 22 Dec 2011 09:07:30

This makes retrieving and sorting by run date and time very challenging, as conversion to the DATETIME data type must be undertaken each time a row is retrieved.

I therefore created this small scalar function to do the conversion which can be called inline. Performance is acceptable (as all the calculations/concatenations are done using local variables and built-in functions), but then there really isn't any choice but to do it this way.

Code

DROP FUNCTION dbo.udf_IntToDateTime

GO

CREATE FUNCTION dbo.udf_IntToDateTime (@RunDate INT, @RunTime INT) RETURNS DATETIME

BEGIN

/*************************************************************

Purpose: Takes a pair of integers representing date and time

in the form YYYYMMDD and HHMMSS

and converts them to a date time string

(using format 113 as this is familiar and convenient)

This date time string is then converted to DATETIME datatype

and returned as the result.

Author: ChillyDBA

History: 4 May 2010 - Initial Issue

***************************************************************/

DECLARE @RunDateText VARCHAR(16),

@RunTimeText VARCHAR(12),

@OutputText VARCHAR(20),

@OutputDate DATETIME

SELECT @RunDateText = '00000000' + CAST(@RunDate AS VARCHAR(16)),

@RunTimeText = '000000' + CAST(@RunTime AS VARCHAR(12))

SELECT @RunDateText = RIGHT(@RunDateText ,8),

@RunTimeText = RIGHT(@RunTimeText ,6)

SELECT @OutputText =

SUBSTRING(@RunDateText, 1, 4) + '-' +

SUBSTRING(@RunDateText, 5, 2) + '-' +

SUBSTRING(@RunDateText, 7, 2) + ' ' +

SUBSTRING(@RunTimeText, 1, 2) + ':' +

SUBSTRING(@RunTimeText, 3, 2) + ':' +

SUBSTRING(@RunTimeText, 5, 2)

SELECT @OutputDate = CAST(@OutputText AS DATETIME)

RETURN CONVERT(DATETIME, @OutputDate, 113)

END

--select dbo.udf_IntToDateTime (20091013, 15000)

--select dbo.udf_IntToDateTime (20091013, 150000)