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. 
        , 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.


David Sumlin said...

Amazing that this is the first time I've ever seen this function mentioned. Thank you!

Post a Comment