Ora 01843 not a valid month ошибка

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';

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;

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';

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;

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;

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;

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');

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.

Introduction

If you’re an Oracle SQL developer, you would have seen the «not a valid month» error quite a lot in your career. In this article, I’ll discuss what the error means and a few ways you can solve it.

Background

What Is The «Not a Valid Month» Error?

This error message appears in Oracle when you’re using the TO_DATE function in an SQL statement, but you get an error with the output:

ORA-01843: not a valid month

The TO_DATE function should be converting your input into a DATE value, but there is some error happening that’s preventing you from doing this.

TO_DATE Syntax

The syntax of the TO_DATE function is:

TO_DATE( string1, [ format_mask ], [nls_language ] )

The first parameter is your input string, and is the only mandatory field. The second parameter is the format mask of the input value, and the third is the language of the date value. We’ll cover both of these below.

Causes and Fixes for the Error

There are a few causes of this error.

First of all, the most common cause is how you’ve specified the month value inside your TO_DATE parameter. 

It can often be a typo, such as entering a value of «13» for the month (as there are only 12 months) or entering a value of «JNA» instead of «JAN» for January.

SELECT TO_DATE('01-JNA-2015') FROM dual;

Fix: To fix this, update your SQL statement to remove the mistake and use the correct month value.

SELECT TO_DATE('01-JAN-2015') FROM dual;

If the value is correct, and you’re still getting the error, it could be to do with the format you’ve entered.

TO_DATE allows you to enter a format along with the input value. The format reflects what the input value is, not the output value, like some other functions. The output value is always a DATE, so it doesn’t need a format. The input value does need a format.

If you’ve entered a valid value for the month, such as «JAN» or 12, then it might be that your format does not match up to the input value.

For example:

SELECT TO_DATE('14-APR-2015', 'MM-DD-YYYY') FROM dual;

This query will show you an error because the value expected for the month is in the wrong order, and 14 is too high of a value for the month.

Fix: Either update the input value to match the format, or update the format to match the input value.

SELECT TO_DATE('14-APR-2015', 'DD-MON-YYYY') FROM dual;

Finally, if neither of those solutions work, or if you’re not specifying a format value, then it is most likely a database setting.

Sessions on your database are created in a certain language format or date format. These are set up when Oracle is installed, but can be modified for a session.

This is relevant because different locations and countries in the world have different ways of specifying dates.

You can find out what your database is doing by querying two values on your database.

The first is the NLS_DATE_LANGUAGE

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE';

PARAMETER             VALUE
NLS_DATE_LANGUAGE     ENGLISH

Your query should show something that is related to your location, such as ‘AMERICAN’. My example shows ‘ENGLISH’ as I’m based in Australia.

The second value, and probably the more important value for this error, is the NLS_DATE_FORMAT.

SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';

PARAMETER           VALUE
NLS_DATE_FORMAT     DD/MON/RR

This will show you the actual format that dates are expected to be in, if the format is not specified in the function.

As you can see, my format is ‘DD/MON/RR’. This is the format that the TO_DATE function expects. If the input I’m supplying is different to this, I’ll get an error.

SELECT TO_DATE('05-22-2015') FROM dual;

ORA-01843: not a valid month

If you’re getting this error, it could be because your input value does not match the format for your database.

Fix: There are a couple of ways to fix this. Either change the input value in your function to match your format, specify a format inside your function.

You can also change the value of this database parameter for your session by using the ALTER SESSION command.

ALTER SESSION SET NLS_DATE_FORMAT = 'MM-DD-YYYY';
SELECT TO_DATE('05-22-2015') FROM dual;

05-22-2015

So, the «not a valid month» error can be resolved after looking into a few different causes. Most likely it’s a typing error or format mismatch, but there are a few ways to check.

History

Keep a running update of any changes or improvements you’ve made here.

[{«Product»:{«code»:»SSEP7J»,»label»:»Cognos Business Intelligence»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w\/o TPS»},»Component»:»Metrics Designer»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»10.2.2;10.2.1;10.2″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]