Skip to content

How to Resolve ORA-01843 Error Message

ORA-01843: not a valid month

ORA-01843 means that SQL engine cannot recognize the month value of your input string under current NLS_DATE_LANGUAGE. In this post, I will talk about some invalid month problems of ORA-01843 listed below:

  1. Misspelled Month Value
  2. Unmatched Month Value
  3. Foreign Month Value
  4. Compares Date with String

Misspelled Month Value

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('Augut 30, 2019', 'Month dd, YYYY') from dual;
select TO_DATE('Augut 30, 2019', 'Month dd, YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

In the above TO_DATE example, we misspelled "August" as another one, that's why SQL engine cannot recognize the month value. The solution is easy, please recheck your spelling and correct it to make it valid.

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('August 30, 2019', 'Month dd, YYYY') from dual;

30-AUG-19

Unmatched Month Value

You might see there's nothing wrong in your month value, but the month value 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 "August" in Japanese.

SQL> select TO_DATE('八月 30, 2019', 'Month dd, YYYY') from dual;
select TO_DATE('八月 30, 2019', 'Month dd, YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

Where to Find Valid Month Values?

Indeed, "八月" is also a correct expression of August in Japanese, but it's invalid to Oracle. To solve such error pattern, you have to know valid and correct month values for different NLS_DATE_LANGUAGE. In this case, "八月" is invalid, "8月" is valid.

SQL> select TO_DATE('8月 30, 2019', 'Month dd, YYYY') from dual;

30-8月 -19

Foreign Month Value

For some reason, you might use a foreign month value in your statement like the following:

SQL> select TO_DATE('Août 30, 2019', 'Month dd, YYYY') from dual;
select TO_DATE('Août 30, 2019', 'Month dd, YYYY') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

Under current NLS_DATE_LANGUAGE (Japanese), SQL engine doesn't recognize "Août". You need to tell it how to translate it by adding NLS parameter option to make the month valid. 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 month values for different languages. Consequently, "Août" is August in French.

SQL> select TO_DATE('Août 30, 2019', 'Month dd, YYYY', 'NLS_DATE_LANGUAGE=FRENCH') from dual;

30-8月 -19

Please note that, even though you used a very common English term "August" in the statement, it's still not a valid month value under Japanese environment in case of ORA-01843.

SQL> select TO_DATE('August 30, 2019', 'Month dd, YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') from dual;

30-8月 -19

Compares Date with String

In some cases, we saw users compare a datetime with a string like this:

SQL> select 'TRUE' "Compare" from dual where to_date('1/1/2010 23:20', 'MM/DD/YYYY HH24:MI:SS') < '01/01/2022 00:00:00';
select 'TRUE' from dual where to_date('1/1/2010 23:20', 'MM/DD/YYYY HH24:MI:SS') < '01/01/2022 00:00:00'
                                                                                   *
ERROR at line 1:
ORA-01843: not a valid month

In fact, a date value cannot compare with a string one, we should compare them on the same base.

SQL> select 'TRUE' "Compare" from dual where to_date('1/1/2010 23:20', 'MM/DD/YYYY HH24:MI:SS') < to_date('01/01/2022 00:00:00', 'MM/DD/YYYY HH24:MI:SS');

Comp
----
TRUE

As you can see, I use TO_DATE function on both operands to compare them on the same base.

To better know how to convert a string into date value, you may check: Oracle TO_DATE Function Examples.

2 thoughts on “How to Resolve ORA-01843 Error Message”

  1. select TO_DATE(‘Août 30, 2019’, ‘Month dd, YYYY’, ‘NLS_DATE_LANGUAGE=FRENCH’) from dual;

    Can you get example in ARABIC

Leave a Reply

Your email address will not be published. Required fields are marked *