Small Rant MSDB.DBO.Sysschedules

• oogibah

  SSC Eights!

  Points: 827

  More actions

  April 10, 2019 at 6:34 pm

  #3096665

  WHY IN THE WORLD IS THE ACTIVE START AND END TIME STORED AS AN INT?!

   

  I wanted to create some tlog backups using a script to say make the tlog schedule 15 minutes earlier than the diffs

  If it was stored as a Time(0) I could simply do a dateadd(minutes,@difftime,-15)

  but NOOOOOOOOOO

  I had to do this BS

  SELECT

  STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), SS.[active_start_time]), 6), 5, 0, ':'), 3, 0, ':') AS StaertTime

  , CONVERT(TIME(0), DATEADD(MINUTE, -15, STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), SS.[active_start_time]), 6), 5, 0, ':'), 3, 0, ':')), 114) AS NewTime

  FROM

  msdb.dbo.sysjobs S

  LEFT JOIN msdb.dbo.sysjobschedules SJ

  ON S.job_id = SJ.job_id

  LEFT JOIN msdb.dbo.sysschedules SS

  ON SS.schedule_id = SJ.schedule_id

  WHERE

  S.name LIKE '_%Diff_%'

  AND S.enabled <> '0';

   

  /endrant

• Ricky Lively

  Right there with Babe

  Points: 729

  More actions

  April 11, 2019 at 1:22 pm

  #3124201

  Have you tried using msdb.dbo.agent_datetime?