converting active_start_time in sysjobschedules?

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  November 9, 2007 at 7:57 am

  #122202

  i've got a query that looks at the active_start_time from the msdb..sysjobschedules table.

  is there a way to convert the values?

  it's an int datatype.

  heres an example of the values displayed.

  80000

  100

  200500

  200500

  100

  80000

  80000

  i would like to see it as 8:00, or 11:00 or what have you,

  or even in 24 hour clock would be find.

  any ideas?

  _________________________

• Chuck Rivel

  SSCrazy

  Points: 2991

  More actions

  November 9, 2007 at 9:53 am

  #749914

  from bol:

  Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.

  so, to get the data to look like a HH:MM:SS format, the following SQL would work:

  select left(right('0' + cast(active_start_time as varchar), 6), 2) + ':' +

  substring(right('0' + cast(active_start_time as varchar), 6), 3,2) + ':' +

  right(cast(active_start_time as varchar), 2)

  from msdb..sysschedules

  hope this helps

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  November 9, 2007 at 9:55 am

  #749916

  many thanks chuck!

  _________________________

• neotokyo

  SSCertifiable

  Points: 7239

  More actions

  November 9, 2007 at 9:57 am

  #749919

  getting the following value for the

  monthly frequency:

  00::0

  is ok though. i'm not worried about that as long as most of it

  work ok.

  _________________________

• niall.baird

  Hall of Fame

  Points: 3468

  More actions

  May 22, 2011 at 7:05 pm

  #1328270

  I know this is old, but I never thought I'd use SQL 2000 again....until today :crying:

  select j.[name]

  , case freq_type when 1 then 'Once'

  when 4 then 'Daily'

  when 8 then 'Weekly'

  when 16 then 'Monthly'

  when 32 then 'Monthly relative'

  when 64 then 'When SQL Server Agent starts'

  END 'freq_type'

  , CASE s.freq_type

  WHEN 4 THEN 'DAILY'

  WHEN 8 THEN CASE s.freq_interval WHEN 1 THEN ' on Sunday'

  WHEN 2 THEN ' on Monday'

  WHEN 4 THEN ' on Tuesday'

  WHEN 8 THEN ' on Wednesday'

  WHEN 16 THEN ' on Thursday'

  WHEN 32 THEN ' on Friday'

  WHEN 64 THEN ' on Saturday'

  END

  WHEN 16 THEN CASE s.freq_interval WHEN 1 THEN convert(varchar,s.freq_interval) + 'st day of the month'

  WHEN 2 THEN convert(varchar,s.freq_interval) + 'nd day of the month'

  WHEN 3 THEN convert(varchar,s.freq_interval) + 'rd day of the month'

  ELSE convert(varchar,s.freq_interval) + 'th day of the month'

  END

  WHEN 32 THEN CASE s.freq_interval WHEN 1 THEN 'Sunday'

  WHEN 2 THEN 'Monday'

  WHEN 3 THEN 'Tuesday'

  WHEN 4 THEN 'Wednesday'

  WHEN 5 THEN 'Thursday'

  WHEN 6 THEN 'Friday'

  WHEN 7 THEN 'Saturday'

  WHEN 8 THEN 'Day'

  WHEN 9 THEN 'Weekday'

  WHEN 10 THEN 'Weekend day'

  END

  END 'days'

  , left(right('0' + cast(active_start_time as varchar), 6), 2) + ':' +

  substring(right('0' + cast(active_start_time as varchar), 6), 3,2) + ':' +

  right(cast(active_start_time as varchar), 2) 'Start Time'

  , case when j.enabled = 1 then 'Enabled'

  ELSE 'Disabled'

  END as Enabled

  , '' as Written

  from sysjobschedules s

  inner join sysjobs j

  on s.job_id = j.job_id

  where j.[name] like 'AB.Schedule%'

  order by j.enabled desc, j.[name]