November 18, 2008 at 3:46 pm
Hi,
Please help me in converting the output from sysjobschedules under msdb database to a valid date and time format.
I have one job scheduled for execution at 4:00 AM but the output of below mentioned query gives me 40000 which is invalid as it should be 040000.
select next_run_date,next_run_time from msdb..sysjobschedules
Please tell me how I can convert it to 040000 as I need it to be in this format for tracking some information about jobs.
TIA,
MJ
November 18, 2008 at 4:29 pm
try this
select next_run_date,case when next_run_time < 100000 then '0' + cast(next_run_time as varchar) else cast(next_run_time as varchar) end as next_run_time
from msdb..sysjobschedules
November 18, 2008 at 4:54 pm
The code below has a couple of options you can use:
select
next_run_date,
right(10000000+next_run_time,6) as Time1,
stuff(stuff(right(10000000+next_run_time,6),5,0,':'),3,0,':') as Time2
from
msdb..sysjobschedules
Results:
next_run_date Time1 Time2
------------- ------ --------
20081119 153000 15:30:00
20081119 021500 02:15:00
20081119 021500 02:15:00
20081119 020000 02:00:00
November 19, 2008 at 1:59 pm
It worked like a charm. Thanks a lot guys.
Manu
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply