in 11g, How to ..., Oracle Database 946 Words

Get next date

In a project we need to have a function that returns next shipping date, the idea is that when a date is added the function should evaluate that date and return next correct shipping date.
Weekend should be skipped and also public holidays. Since we have business around the world we need to be able to handle several “public holiday lists”

By using functions in dbms_scheduler we managed to solve it, this is how we did it.
First you give the public holiday list a name, like US or Sweden etc. Then you apply the dates that are public holidays, you can add other dates as well if the company has certain days of the year where no shipping should take place. We will create a schedule for the dates that we want to be removed when evaluating next shipping date.

exec dbms_scheduler.create_schedule
(
schedule_name=>'MyList',
repeat_interval => 'FREQ=YEARLY;BYDATE=1224,1225,1226'
);

Here I have created a schedule named MyList and i added three dates December 24,25 and 26 which are public holidays in Sweden. The frequency of this schedule is yearly.

So now we have the schedule with dates that we want to be omitted, now we need the function that evaluates the date. DBMS_SCHEDULER has a built in function called evaluate_calendar_string that we will use.

set serveroutput on
declare
retDate TIMESTAMP;
begin

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
calendar_string => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;
BYHOUR=13;BYMINUTE=0;BYSECOND=0;exclude=MyList',
start_date => SYSTIMESTAMP+1,
return_date_after => SYSTIMESTAMP,
next_run_date => retDate);

dbms_output.put_line(to_char(retDate));
end;
/

So we supply a

CALENDAR_STRING

that tells the function how to evaluate the date, in our case it is FREQ=weekly and BYDAY=Monday to Friday is valid days. Weekends is not valid for us, but it can be that you do shipping on Saturdays then you can add that or shipping just happens Tuesdays and Thursdays then you can change to that.

The (BYHOUR;BYMINUTE;BYSECOND) is used to evaluate the time within the date, as i have specified here 13:00:00 means that if the time stamp is before 13:00:00 it is evaluated to the same day and if it is after 13:00:00 it is evaluated to the next day, and the time stamp for next day is set t o 13:00:00 (see the example below)

EXCLUDE tells us which schedule we should use to exclude dates, in my case it is the MyList which excludes December 24,25 and 26.

START_DATE

is the date that you want to be evaluated.

RETURN_DATE_AFTER

This is what the manual says:
argument helps the Scheduler determine which one of all possible matches (all valid execution dates) to return from those determined by the start_date and the calendar string.When a NULL value is passed for this argument, the Scheduler automatically fills in systimestamp as its value.

RetDate

Is where the new date is stored

So if I call this function I’ll get:

 1 declare
 2
 3 retDate TIMESTAMP;
 4 sDate TIMESTAMP := '2014-11-25 12:27:32,750694';
 5 begin
 6
 7 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
 8 calendar_string => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=13;BYMINUTE=0;BYSECOND=0;exclude=MyList',
 9 start_date => sDate,
 10 return_date_after => sDate,
 11 next_run_date => retDate);
 12 dbms_output.put_line(to_char(retDate));
 13 end;
 14 /

2014-11-25 13:00:00,800000

Here I supply a date in the middle of the week with time stamp before 13:00:00, as you see the date is evaluated to the same date but the time is set to 13:00:00

 1 declare
 2
 3 retDate TIMESTAMP;
 4 sDate TIMESTAMP := '2014-11-25 13:27:32,750694';
 5 begin
 6
 7 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
 8 calendar_string => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=13;BYMINUTE=0;BYSECOND=0;exclude=MyList',
 9 start_date => sDate,
 10 return_date_after => sDate,
 11 next_run_date => retDate);
 12 dbms_output.put_line(to_char(retDate));
 13 end;
 14 /

2014-11-26 13:00:00,800000

So here I supply a day in the middle of the week and time stamp after 13:00:00, the date is evaluated to the next day with time stamp set to 13:00:00

 1  declare
 2
 3 retDate TIMESTAMP;
 4 sDate TIMESTAMP := '2014-11-28 13:27:32,750694';
 5 begin
 6
 7 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
 8 calendar_string => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=13;BYMINUTE=0;BYSECOND=0;exclude=MyList',
 9 start_date => sDate,
 10 return_date_after => sDate,
 11 next_run_date => retDate);
 12 dbms_output.put_line(to_char(retDate));
 13 end;
 14 /

2014-12-01 13:00:00,800000

Here I supply a Friday and time after 13:00:00 and as you see the next shipping date will be next Monday at 13:00:00.

 1 declare
 2
 3 retDate TIMESTAMP;
 4 sDate TIMESTAMP := '2014-12-24 13:27:32,750694';
 5 begin
 6
 7 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
 8 calendar_string => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=13;BYMINUTE=0;BYSECOND=0;exclude=MyList',
 9 start_date => sDate,
 10 return_date_after => sDate,
 11 next_run_date => retDate);
 12 dbms_output.put_line(to_char(retDate));
 13 end;
 14 /

2014-12-29 13:00:00,800000

More examples, now I gave December 24:th as date, this is a public holiday in Sweden and so is December 25 and 26. So since I excluded myList next shipping date is December 29th. But what happened to December 27 and 28 ? As you might have guessed these are Saturday and Sunday that week.

 1 declare
 2
 3 retDate TIMESTAMP;
 4 sDate TIMESTAMP := '2014-11-25 11:27:32,750694';
 5 begin
 6
 7 DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
 8 calendar_string => 'FREQ=WEEKLY;BYDAY=MON,TUE,WED,THU,FRI;exclude=MyList',
 9 start_date => sDate,
 10 return_date_after => sDate,
 11 next_run_date => retDate);
 12 dbms_output.put_line(to_char(retDate));
 13 end;
 14 /

2014-11-26 11:27:32,000000

This is my last example,here I have removed the time info in the FREQ details, so time is not evaluated as in my prior examples. Date returned is the day after and time stamp is the same as given.

Leave a comment