Function Next_Day in pl/sql-CodePudding

Function is not working. What is wrong? I use database 18c xe.

CodePudding user response：

It works, but you should use language your database speaks. Mine speaks Croatian.

(Just setting date format; you don't have to do that):

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';

Session altered.

Your query doesn't work in my database either:

SQL> select next_day(sysdate, 'MONDAY') from dual;
select next_day(sysdate, 'MONDAY') from dual
                         *
ERROR at line 1:
ORA-01846: not a valid day of the week

But, if I use Croatian name for Monday, then it works:

SQL> select next_day(sysdate, 'PONEDJELJAK') from dual;

NEXT_DAY(S
----------
03.01.2022

Or, alter session - then the 1st query works as well:

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE = 'ENGLISH';

Session altered.

SQL> select next_day(sysdate, 'MONDAY') from dual;

NEXT_DAY(S
----------
03.01.2022

SQL>

You're from ... Poland? Follow what I suggested and try with "poniedziałek".

CodePudding user response：

select next_day(sysdate, 'mon') from dual

can you try this one?

set the nls_date_language as per your language AND TRY

alter session set NLS_DATE_LANGUAGE = "ENGLISH";