Not a valid month oracle ошибка

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.

While working with Oracle database, have you ever come across any error stated as ORA-01843: not a valid month error? Are you looking for ways to fix not a valid month error? If yes, then do you want to know how to resolve not a valid month error in oracle? Yes, here, in this blog, I am going to share the most effective methods that can be tried to fix ORA-01843: not a valid moth error.

So, do not wait for anything, let’s get started with the introduction of this error, its causes and obviously the fixes to resolve ora-01843 error.

What Is ORA-01843: Not a Valid Moth Error?

Generally, users get this error when the SQL engine cannot recognize the month value of the input string. ORA-01843 error in Oracle represents not a valid month error which is very common and is encountered while working with variables of datatype like TO_DATE. The TO_DATE function in Oracle should be converting the input into a DATE value. But, sometimes there occurs some problem due to which it prevents you to do so.

The correct syntax of TO_DATE function is as follows:

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

Here, the first parameter is the input string and it is the only mandatory field. And, the second parameter denotes the format mask of the input value, however, the third parameter is the data value’s language.

There are some major reasons due to which you get ORA-01843 not a valid month error in Oracle. Below, we will discuss it…..

Two major causes are responsible for causing ora-01843: not a valid month error which is mentioned below:

• The date that has been entered does not match the date format of the database that has been mentioned in the NLS_DATE_FORMAT variable.
• The value of the month that has been entered min the date format does not represent a valid month. It means that the month’s value is not between January To December or 01-12.

How To Resolve Not A Valid Month Error In Oracle?

Well, when it comes to fixing ORA-01843: nota a valid month error then there are some factors that have to be kept in mind. Yes, it is very important to first examine that why are you getting this error or you can say which error pattern of ora-01843 error you are getting. Based on the error patter, you can resolve not a valid month error in Oracle easily. So, let’s first know about the error pattern and then fix the error one by one. Some of the error pattern of ORA-01843: not a valid month error are:

#1: Unmatched Month Value

#2: Misspelled Month Value

#3: Foreign Month Value

#1: ORA-01843 Error Due to Unmatched Month Value

It is quite possible that you will that there is nothing wrong in your month value, however, the month value you used may not actually be acceptable by Oracle. For example:

Let’s first switch NLS_DATE_LANGUAGE to JAPANESE

The code should be:

SQL> alter session set nls_date_language=’Japanese’;

Session altered.

Next, 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

Now, here the question arises where you would find a correct month value…

As I have mentioned already that “八月” is also a correct expression of month ‘August’ in Japanese language, but it is completely invalid in Oracle.

Solution To Fix This Issue

In order to solve this error pattern, it is very important to know the correct and the valid month values for different NLS_DATE_LANGUAGE. Well, here, in this case, “八月” is not a valid value but “8月” is a valid value for month. The code should be written as:

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

30-8月 -19

#2: ORA-01843 Error Due To Misspelled Month Value

While using Oracle database, there might be types in the statement just like the below 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’;

AMERICANSQL> 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

Here, in the above code of TO_DATE example, there is a misspelled ‘August’ as another one, this is why SQL engine cannot recognize the month value. For this case, the solution is so easy, you just have to check the spelling and correct in right there.

Solution To Fix This Issue

If there is nothing wrong then the reason of causing this error may be that you should paste the input string into a text editor and then make it run spell checker. As for example, the spell checker plugins users used to check spellings of Notepad++.

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.

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

#3: ORA-01843 Error Due To Foreign Month Value

You might sometime use a foreign month value due to some reason in the statement like the below code:

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

Now, under the current language, i.e., NLS_DATE_LANGUAGE, SQL engine does not know what is the meaning of Août here? Here, you have to tell how to translate it by simply adding NLS parameter option. However, another question arises here is – what language should be used to translate it?

Solution To Fix This Issue

To know what NLS_DATE_LANGUAGE should be used in order to translate the string, you have to check the valid month values for different languages. However, Août is August in French. So, the code should be written as:

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

30-8月 -19

Please make sure that you used a very common English term states as August in the statement, here you still have to translate August under Japanese environment when you get ORA-01843: not a valid month error.

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

30-8月 -19

Ultimate Solution: Try Oracle File Repair Tool To Resolve Not a Valid Month Error

In order to know how to resolve not a valid moth error in Oracle, you can use Oracle File Repair Tool. If after using the above all mentioned ways to fix ora-01843 error, you are still unsuccessful then you should try this repair tool. This tool has the capability to repair not a valid month error in oracle. This tool has some great features that allow users to fix any kind of error they get while using Oracle database and recover the database easily.

Steps To Resolve Not a Valid Month Error

Step 1: Search the Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases on your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

---