This SQL query works:
SELECT NEXT_DAY (sysdate, 7) "NEXT DAY" FROM DUAL; NEXT DAY ------------------- 2021-08-15 00:31:12
But in PL/SQL, the same function doesn't work:
declare nextday date := NEXT_DAY (sysdate, 7); begin null; end; /
ORA-01846: not a valid day of the week
Wouldn't want to hardcode the days of the week in the second parameter in English, like this:
NEXT_DAY(SYSDATE, 'SATURDAY') --или NEXT_DAY(SYSDATE, 'SUNDAY')
So far I'm using the following solution:
dteExpires DATE := NEXT_DAY (SYSDATE, TO_CHAR(TO_DATE('2021-08-15', 'YYYY-MM-DD'), 'DAY'));
I would like to know why
NEXT_DAY() behaves differently in PL/SQL than in SQL?
A loose translation of the question NEXT_DAY function works differently in SQL vs. PL/SQL? by @wweicker
If you look at the documentation for the SQL function NEXT_DAY , you'll notice that passing a number to represent the day of the week is not documented. For some reason it works, but if you rely on it, then there is a risk that in the future Oracle will change the implementation to comply with the specification of this function (the risk is small, but still).
The PL/SQL implementation only works with the documented specification – it does not accept a numeric parameter to represent the day of the week.
Given the documentation, the question is better put like this: Why does this work in SQL? .
Only Oracle developers can answer this question.
Perhaps this discussion on OTN will be helpful, or this too .
Free translation of answer from @mathguy