Background.
A working week in the United Kingdom is Mon-Fri and the weekend being Sat-Sun. This is true for most western countries, including the United States.
The remit was to write a database API which takes in a date and gets the next working date. The API should also take into account UK public holidays and should not include these as a working day. The function can be used via calls within APEX, the database or as a RESTful API.
Proposed Solution.
On paper this looks simple. With a rough sketch of the code being as follows.
FUNCTION f_get_next_working_date (f_date IN DATE)
l_date DATE := f_date+1;
BEGIN
WHILE (f_date_is_PublicHoliday (f_date => l_date) OR
TO_CHAR(l_date, 'D') IN (6, 7)
)
LOOP
-- We have hit a public holiday or weekend
l_date := l_date + 1;
END LOOP;
RETURN l_date
END f_get_next_working_date; The Oracle TO_CHAR function is used to return the numerical day of the week (1-7). With the assumption 6 and 7 (Sat and Sun) being the weekend.
TO_CHAR (l_date, 'D')When unit testing this from within the database all worked fine. However, when the API was ran via APEX I was getting strange results. These were,
A given date of Fri 07-OCT-2022 would give Sun 09-OCT-2022 as next working day.
A given date of Sat 08-OCT-2022 would give Sun 09-OCT-2022 as next working day.
What is going on?
"Make assumption if you must, but do not make the mistake of assuming that your assumption is always right".
After some research, I found that in the United States and Canada, the first day of the week is Sunday. Since I made the assumption of Sunday being the 7th day of the week, this made my API to give inconsistent results - depending who and when the call was made.
But how is APEX influencing the first day of the week?
The Application Primary Language was set to English (United States) (en-us) which when the application is run, it implicitly sets the territory. You can see this in the debugger as
alter session set NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_SORT='BINARY' NLS_COMP='BINARY' NLS_CALENDAR='GREGORIAN'and there you have it. How the APEX Primary Language can have an influence to things you never thought of.
So what is the solution?
There ware several solutions, such as setting the Application Primary Language to English (United Kingdom) (en-gb)
In the end I amended my API to use abbreviated day of the week (MON, TUE, WED, etc etc) in my TO_CHAR function to work out if my proposed date falls on the weekend of SAT or SUN.
TO_CHAR (l_date, 'DY')