MSDB - 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)

ċ
udf_IntToDateTime.sql
(1k)
Andy Hughes,
Jun 22, 2012, 6:51 AM
Comments