/* Mostly about Oracle APEX, ORDS, PL/SQL and a bit of Linux in between */
PL/SQL: Identify dates of weekdays within time period using NEXT_DAY() function
Today I would like to show an example to demonstrate how you can identify all dates of occurrences of a single weekday within a period of time in PL/SQL by specifying a weekday.
For a project, a form mask in APEX was needed on which the operator should be able to select one or more weekdays (Monday, Tuesday, etc.) within a period of time and specify a time of day and a coach. The aim was to calculate the dates of all selected weekdays between the start and end date with PL/SQL and to store the data in the database table “appointments”. End users can book a support slot on these days via a separate order mask.
First, we need to create the specification of the PL/SQL package with its contents as shown below:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
create or replace package booking_dates_pkg as /* Calls procedure save_dates for each weekday * @param pi_coach: e.g. 'MWILHELM' - v('APP_USER') * @param pi_period_from: e.g. 23.04.2021 - Start date (DD.MM.YYYY) * @param pi_weekdays: e.g. '0:1:2:3:4' (Monday - Friday) * @param pi_period_to: e.g. 07.05.2021 - End date (DD.MM.YYYY) * @param pi_period_time: e.g. '12:00' - Daytime */ procedure all_weekdays_in_period ( pi_coach in varchar2 , pi_period_from in date , pi_weekdays in varchar2 , pi_period_time in varchar2 , pi_period_to in date ); /* Identifies each date of a passed weekday within the period * @param pi_coach: e.g. 'MWILHELM' - v('APP_USER') * @param pi_period_from: e.g. 23.04.2021 - Start date (DD.MM.YYYY) * @param pi_weekday: e.g. 0 (Monday) * @param pi_period_to: e.g. 07.05.2021 - End date (DD.MM.YYYY) * @param pi_period_time: e.g. '12:00' - Daytime */ procedure save_dates ( pi_coach in varchar2 , pi_period_from in date , pi_weekday in number , pi_period_time in varchar2 , pi_period_to in date ); end booking_dates_pkg; |
Next, the corresponding package body can be generated:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
create or replace package body booking_dates_pkg as procedure all_weekdays_in_period ( pi_coach in varchar2 , pi_period_from in date , pi_weekdays in varchar2 , pi_period_time in varchar2 , pi_period_to in date ) as begin for i in (select to_number(regexp_substr(pi_weekdays, '[^:]+', 1, level)) as day from dual connect by to_number(regexp_substr(pi_weekdays, '[^:]+', 1, level)) is not null) loop save_dates( pi_coach ⟹ pi_coach , pi_period_from ⟹ pi_period_from , pi_weekday ⟹ i.day , pi_period_time ⟹ pi_period_time , pi_period_to ⟹ pi_period_to ); end loop; exception when others then raise; end all_weekdays_in_period; ------------------------------------------------------------------------------------------------------------------- procedure save_dates ( pi_coach in varchar2 , pi_period_from in date , pi_weekday in number , pi_period_time in varchar2 , pi_period_to in date ) as l_day date; l_weekday_name varchar2(30); l_period_from_year number; l_period_to_year number; l_count_days number; l_count_holidays number; begin l_period_from_year := extract(year from pi_period_from); l_period_to_year := extract(year from pi_period_to); /* count all weekdays of a single weekday (e.g. Monday) within the period * function next_day() depends of the invoking session's NLS_DATE_LANGUAGE and does not allow the specification of NLS values to make it independent * workaround: to make function next_day() independent, use trunc(sysdate, 'IW') to get the first day of week (Monday) defined by ISO standard, * and add number of days pi_weekday to identify the name of the selected day of the week uniquely */ l_weekday_name := to_char(trunc(sysdate, 'IW') + pi_weekday, 'Day'); l_count_days := ( ( next_day ( pi_period_to - 7, l_weekday_name ) - next_day ( pi_period_from - 1, l_weekday_name ) ) / 7 ) + 1; for rec_days in 0..l_count_days-1 loop /* identify the weekday (subtract 1 day to include the start date as well) and all following occurrences within the period by calculating weekday+7*rec_days */ l_day := ( next_day ( pi_period_from - 1, l_weekday_name ) + 7 * rec_days ); select count(holiday_date) into l_count_holidays from table(german_holidays(l_period_from_year, l_period_to_year)) where holiday_date = l_day; if l_count_holidays = 0 then insert into appointments(date, booked, coach) values (to_date(to_char(l_day, 'DD.MM.YYYY') || ' ' || pi_period_time, 'DD.MM.YYYY HH24:MI'), 0, pi_coach); end if; end loop; exception when others then raise; end save_dates; end booking_dates_pkg; |
Via the database function NEXT_DAY() the date of the weekday name passed in the second parameter will be returned which follows the specified date. As an example, NEXT_DAY(’23-AUG-2020′, ‘SUNDAY’) would return the following Sunday (August 30, 2020), even though 23 August is also a Sunday.
The parameter for determining the returning day of the week depends on the NLS_DATE_LANGUAGE of the calling session, at the same time the function does not allow the free specification of NLS values. For this reason, calling NEXT_DAY(’23-AUG-2020′, ‘SUNDAY’) may result in the ORA error ORA-01846: not a valid day of the week if a different language (English, in the example above) is stored for NLS_DATE_LANGUAGE within the session. To use the function independently and get the weekday name in the language of the session, we use trunc(sysdate, ‘IW’) as a workaround to get the first day of the week according to ISO standard (Monday). To identify which weekdays have been selected on the website, colon-separated return values (from 0 (= Monday) to 4 (= Friday)) are passed as varchar from the APEX page to the procedure all_weekdays_in_period (e.g. ‘0:1:3’ for Monday, Tuesday and Thursday) that are added on top of Monday. Via to_char(trunc(sysdate, ‘IW’) + pi_weekday, ‘Day’) we get the selected weekday in the correct language of the session. After determining the specific date of the weekday, all following occurrences of this weekday can be identified by simple mathematical multiplications using a FOR loop. In order for the start day to be taken into account, we use NEXT_DAY(〈start date〉-1, 〈weekday name〉) to obtain the date of the day preceding the start date and use this for the calculations. Via the calculation NEXT_DAY( pi_period_from – 1, l_weekday_name ) + 7 * rec_days we get all further occurrences of this weekday with each run of the loop.
Dates of public holidays should not be saved in the database. To check whether a date represents a public holiday, the function german_holidays described in the previous blog post is used at this point.
Related posts:
How to Find the Next Business Day and Add or Subtract N Working Days with SQL (blogs.oracle.com)
Make NEXT_DAY() independent of the invoking session’s NLS_DATE_LANGUAGE (community.oracle.com)
Add optional NLS_DATE_LANGUAGE parameter to NEXT_DAY() (community.oracle.com)