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.

2 comments:

David Sumlin said...

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

exp_nayon said...

Dyson V6 is an incredible cordless vacuum cleaner. This cleaning machine is famous for its lightweight and powerful features. It can be used to clean a wide range of surfaces, ranging from hard floors to ceilings to carpets. The brand specializes in the creations of primary and secondary vacuum cleaners at affordable rates. Let's go for an incredible article about dyson v6 review.

if you want more just look here "dyson V6 review"

Post a Comment