November 9, 2007 at 7:57 am
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?
_________________________
November 9, 2007 at 9:53 am
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
November 9, 2007 at 9:55 am
many thanks chuck! 🙂
_________________________
November 9, 2007 at 9:57 am
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.
_________________________
May 22, 2011 at 7:05 pm
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]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply