Monday, January 19, 2015

Using "msdb.dbo.agent_datetime" to Simplify Working with sysjobhistory

Just learned about the undocumented msdb.dbo.agent_datetime() function. Where has this been all my life? It takes an 8 digit date and a 6-digit time and converts it to a DATETIME - quite a time-saver.

-- An example of where it's really handy. 
   select j.name
        , jh.run_date
        , jh.run_time
        , msdb.dbo.agent_datetime(jh.run_date, jh.run_time)   as 'RunDate'
        , jh.step_id
        , jh.step_name
        , jh.message
     from msdb.dbo.sysjobs         j
     join msdb.dbo.sysjobhistory   jh
       on j.job_id = jh.job_id
 order by msdb.dbo.agent_datetime(run_date, run_time) desc

Beats trying to work with those columns yourself... but remember it's undocumented.