Convert repeat_interval from DBMS scheduler to a usable datatype
 

Making a custom job scheduler that pulls job from our oracle DB and I would like to be able to convert a repeat_interval format into something I can use to test against job start times so the app knows when to run the next job.

So far the logic I have pulls the next_start_time field and adds the converted repeat_interval to next_start_time then I test against the current time.

ex:

next_start_time = 9/20/2011 11:50:00.000000 AM -05:00
repeat_interval = FREQ=DAILY;BYHOUR=13;BYMINUTE=30;

public void ConvertInterval()
{
split repeat_interval
insert into TimeSpan
return ConvertedToTimespan
}

next_start_time.add(ConvertedToTimespan)

If (next_start_time <= DateTime.Now)
{ run job }

I split by ; and extract each part then split again by = to extract the numbers. I have 2 different splits because there's a lot of case statements differenciating the Frequency values. Then add them into a time span, and just add that time span to the next_start_time. The thing is that logic does not work.

There's a lot of different match ups for interval values so it makes it a lot harder. I attached a picture of what values and match ups will be used. Does anyone have any other logic or ideas to throw at me, I'm super stumped? Thanks

DBMS Scheduler: http://download.oracle.com/docs/cd/B...58/d_sched.htm

Might as well answer my own question since this post has 50 views or so.

Found out through a peer there is a function in oracle that converts repeat_intervals to dates.

CREATEORREPLACEFUNCTION SUPPORT_SERVICES.CMN_DBMS_REPEAT_INTERVAL_TO_DT(
p_repeat_interval_str INVARCHAR2:=NULL,
p_start_date INTIMESTAMPWITHTIMEZONE:=NULL,
p_return_date_after INTIMESTAMPWITHTIMEZONE:=NULL
)
RETURNTIMESTAMPWITHTIMEZONE
IS
dte TIMESTAMP(6)WITHTIMEZONE;

BEGIN
dte :=NULL;
DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(p_repeat_interval_str, p_start_date, p_return_date_after, dte);
RETURN dte;
ENDCMN_DBMS_REPEAT_INTERVAL_TO_DT;
/