Small Rant MSDB.DBO.Sysschedules

  • oogibah

    SSC Eights!

    Points: 802

    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

    Say Hey Kid

    Points: 661

    Have you tried using msdb.dbo.agent_datetime?

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply