Making date and time sense from the SQL Server Agent job history table

Sometimes you’ve got to wonder why database developers do things the way the do. Take for example the sysjobhistory table in the SQL Server msdb database. Its got two awful columns namely run_date and run_time. Why are they awful you ask? Well because they are integer representations of the date and time. Just look at the following examples to see the stupidity.

run_date run_time Actual
20091123 230000 2009-11-23 23:00:00
20091123 230001 2009-11-23 23:00:01
20091123 234500 2009-11-23 23:45:00
20091124 0 2009-11-24 00:00:00
20091124 0 2009-11-24 00:00:01
20091124 1000 2009-11-24 00:10:00
20091124 3000 2009-11-24 00:30:00
20091124 10000 2009-11-24 01:00:00
20091124 10636 2009-11-24 01:06:36

Now if you’re wanting to do something meaningful with them you’ll want to represent them as a DATETIME data type most likely. After searching for a quick and easy way I decided to hack a quick bit of TSQL up and put it up here so hopefully others might benefit.

SELECT    CONVERT(DATETIME, 
            STUFF(STUFF(CONVERT(VARCHAR, [run_date]), 7, 0, '-'), 5, 0, '-') + 
            ' ' + 
            STUFF(STUFF(RIGHT('00000' + CONVERT(VARCHAR, [run_time]), 6), 5, 0, ':'), 3, 0, ':'), 120)
FROM    [msdb]..[sysjobhistory]
 

Let me know in the comments if you found it useful or if you have a more elegant way. If you use it a lot, make a user-defined function taking in the two values. I leave that in your capable hands.