Working with SQL Agent Durations

• OldFashionGang

  SSC Enthusiast

  Points: 121

  More actions

  August 30, 2012 at 4:25 am

  #1531780

  +5 cent's of mine

  select top 100

  name

  ,

  convert(datetime,(convert(varchar(8),h.run_date)))

  +(

  +substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2)

  +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2)

  +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2)

  )

  begining

  ,

  convert (varchar ,convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4))))+

  +':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2)

  +':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2)

  duration

  ,

  -- EdsTime as begining+duration

  convert( datetime, convert(varchar(8),h.run_date))

  + (

  substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2)

  +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2)

  +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2)

  )

  +

  ( -- add hours

  dateadd (hh, convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4))

  ), 0)

  +

  -- add mins

  dateadd (mi, convert (int,

  substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2)

  ), 0)

  +

  -- add seconds

  dateadd (ss, convert (int,

  substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2)

  ), 0)

  )

  endtime

  from msdb..sysjobhistory h with( nolock), msdb..sysjobs j with( nolock)

  where h.step_id=0 and h.job_id=j.job_id order by instance_id desc

  works for several years to make this:

  

• Neha05

  Default port

  Points: 1494

  More actions

  December 25, 2012 at 8:46 am

  #1571441

  Good article.