Convert MSDB Date and Time INTEGERS to DATETIME
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
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:
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.