ORA-01846: not a valid day of the week
ORA-01846 means that SQL engine cannot recognize the day value of week of your input string under current NLS_DATE_LANGUAGE. In this post, I will talk about some error patterns of ORA-01846 listed below:
ORA-01846 due to Misspelled Day Value of Week
There might be typos in your statement just like the following example.
[oracle@test ~]$ export NLS_LANG=.UTF8
[oracle@test ~]$ sqlplus /nolog
...
SQL> conn hr/hr
Connected.
SQL> set heading off;
SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_LANGUAGE';
AMERICAN
SQL> select TO_DATE('2019-08-30, Freddy', 'YYYY-MM-DD, Day') from dual;
select TO_DATE('2019-08-30, Freddy', 'YYYY-MM-DD, Day') from dual
*
ERROR at line 1:
ORA-01846: not a valid day of the week
In the above example, we misspelled "Friday" as another one in TO_DATE function, that's why SQL engine cannot recognize the day value of week. The solution is easy, please recheck your spelling and correct it.
If you found nothing wrong, maybe you should paste your input string into a text editor and make it run spell checker. For example, the spell checker plugins of Notepad++.
SQL> select TO_DATE('2019-08-30, Friday', 'YYYY-MM-DD, Day') from dual;
30-AUG-19
ORA-01846 due to Unmatched Day Value of Week
You might see there's nothing wrong in your day value of week, but the day value of week you used may not be acceptable by Oracle. Let's see an example.
First of all, switch NLS_DATE_LANGUAGE to JAPANESE.
SQL> alter session set nls_date_language='Japanese';
Session altered.
Then we used a term "きんようび" which also means "Friday" in Japanese.
SQL> select TO_DATE('2019-08-30, きんようび', 'YYYY-MM-DD, Day') from dual;
select TO_DATE('2019-08-30, きんようび', 'YYYY-MM-DD, Day') from dual
*
ERROR at line 1:
ORA-01846: not a valid day of the week
Where to Find Valid Day Value of Weeks?
Indeed, "きんようび" is also a correct expression of Friday in Japanese, but it's invalid to Oracle. To solve such error pattern, you have to know valid and correct day values of week for different NLS_DATE_LANGUAGE. In this case, "きんようび" is not valid, "金曜日" is.
SQL> select TO_DATE('2019-08-30, 金曜日', 'YYYY-MM-DD, Day') from dual;
30-8月 -19
ORA-01846 due to Foreign Day Value of Week
For some reason, you might use a foreign day value of week in your statement like the following:
SQL> select TO_DATE('2019-08-30, Vendredi', 'YYYY-MM-DD, Day') from dual;
select TO_DATE('2019-08-30, Vendredi', 'YYYY-MM-DD, Day') from dual
*
ERROR at line 1:
ORA-01846: not a valid day of the week
Under current NLS_DATE_LANGUAGE (Japanese), SQL engine doesn't know what "Vendredi" means. You need to tell it how to translate it by adding NLS parameter option. But the next question is: what language should we use to translate it?
Where to Find Correct Language?
Again, for mapping what NLS_DATE_LANGUAGE should be used to translate the string, you should check valid day values of week for different languages. Consequently, "Vendredi" is Friday in French.
SQL> select TO_DATE('2019-08-30, Vendredi', 'YYYY-MM-DD, Day', 'NLS_DATE_LANGUAGE=FRENCH') from dual;
30-8月 -19
Please note that, even though you used a very common English term "Friday" in the statement, you still need to translate it under Japanese environment in case of ORA-01846.
SQL> select TO_DATE('2019-08-30, Friday', 'YYYY-MM-DD, Day', 'NLS_DATE_LANGUAGE=AMERICAN') from dual;
30-8月 -19
To better know how to convert a string into date value, you may check: Oracle TO_DATE Function Examples.