Author:    ChillyDBA

Date:        4 May 2010

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:

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. 

 

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)