Not a valid month oracle ошибка

1.

To_Date(To_Char(MaxDate, 'DD/MM/YYYY')) = REP_DATE

is causing the issue. when you use to_date without the time format, oracle will use the current sessions NLS format to convert, which in your case might not be «DD/MM/YYYY». Check this…

SQL> select sysdate from dual;

SYSDATE
---------
26-SEP-12

Which means my session's setting is DD-Mon-YY

SQL> select to_char(sysdate,'MM/DD/YYYY') from dual;

TO_CHAR(SY
----------
09/26/2012


SQL> select to_date(to_char(sysdate,'MM/DD/YYYY')) from dual;
select to_date(to_char(sysdate,'MM/DD/YYYY')) from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select to_date(to_char(sysdate,'MM/DD/YYYY'),'MM/DD/YYYY') from dual;

TO_DATE(T
---------
26-SEP-12

2.

More importantly, Why are you converting to char and then to date, instead of directly comparing

MaxDate = REP_DATE

If you want to ignore the time component in MaxDate before comparision, you should use..

trunc(MaxDate ) = rep_date

instead.

==Update : based on updated question.

Rep_Date = 01/04/2009 Rep_Time = 01/01/1753 13:00:00

I think the problem is more complex. if rep_time is intended to be only time, then you cannot store it in the database as a date. It would have to be a string or date to time interval or numerically as seconds (thanks to Alex, see this) . If possible, I would suggest using one column rep_date that has both the date and time and compare it to the max date column directly.

If it is a running system and you have no control over repdate, you could try this.

trunc(rep_date) = trunc(maxdate) and 
to_char(rep_date,'HH24:MI:SS') = to_char(maxdate,'HH24:MI:SS')

Either way, the time is being stored incorrectly (as you can tell from the year 1753) and there could be other issues going forward.

If you use the DATE data type in your SQL code, it is likely that you have come across the “not a valid month” Oracle database error. As we shall discuss, the error can be fixed with a slight tweak of the SQL.

What is the «not a valid month» Oracle database error?

The “not a valid month” Oracle database error refers to the ORA-01843 error code. This error occurs if the month portion of a DATE type value is not a valid month. The month portion of a DATE value is considered not valid for one of the following reasons:

1. The month value is out of the valid range (for numeric month format)
2. The month name is misspelled (for character month format)
3. The month value is not in the proper format

The “not a valid month” error could be generated when using SQL for table data that includes a column of data type DATE, or TIMESTAMP. The error could also be generated when using SQL functions that accept a date value with a month portion as argument such as the TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ functions. The error could also get generated due to a bug, which is not discussed in this article, and for which a bug report should be submitted.

The “not a valid month” Oracle database error is only generated for the month portion of a date not being valid. For other date-related errors, such as the day or year not being valid, other error codes are output.

This article is based on Oracle Database 19c; for earlier versions such as 12c some differences may exist. As a preliminary step, install Quest^® Toad^® for Oracle, create an instance of Oracle Autonomous Database 19c (or other), and connect to the database instance, all of which are discussed in How to download Toad for Oracle.

What are the relevant initialization parameters?

Three initialization parameters are most relevant, as discussed in the following subsections.

The NLS_DATE_FORMAT parameter

This parameter sets the default format for date values. To list the value of the parameter, run command:

select *

from nls_database_parameters

where parameter = ‘NLS_DATE_FORMAT’;

The output lists the default date format as DD-MON-RR:

PARAMETER                                                                      

———————————————————————————

VALUE                                                          

—————————————————————-

NLS_DATE_FORMAT                                                                

DD-MON-RR                                                                 

It may be set explicitly at the global or session level. Here’s an example of the parameter set at the session level:

ALTER SESSION SET NLS_DATE_FORMAT = ‘mm/dd/yyyy’;

The NLS_TERRITORY parameter

This parameter sets the default territory, which in addition to other settings determines the default value for the NLS_DATE_FORMAT. To list the value of the parameter, run the following command to see the output shown below:

select *

from nls_database_parameters

where parameter = ‘NLS_TERRITORY’;

PARAMETER                                                                      

———————————————————————————

VALUE                                                          

—————————————————————-

NLS_TERRITORY                                                                  

AMERICA                  

It may also be set explicitly at the global or session level. Here’s an example of the parameter set at the session level:

ALTER SESSION SET NLS_TERRITORY = «FRANCE»;

The NLS_DATE_LANGUAGE parameter

This parameter sets the default language for day and month names in date values. To list the value of the parameter, run the following command to see the output shown below:

select *

from nls_database_parameters

where parameter = ‘NLS_DATE_LANGUAGE’;

PARAMETER                                                                      

———————————————————————————

VALUE                                                          

—————————————————————-

NLS_DATE_LANGUAGE                                                              

AMERICAN

It may also be set explicitly at the global or session level. Here’s an example of the parameter set at the session level:

ALTER SESSION SET NLS_DATE_LANGUAGE = «FRENCH»;

What are the valid months

Before we discuss what is not a valid month value, it is imperative to mention what the valid month values are. Different format codes for month require different values. For the MONTH format code, valid months are: January, February, March, April, May, June, July, August, September, October, November, and December. The month value may be specified in upper/lower/mixed case.

For the MON format code, valid month values are: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec. Again, the month value may be specified in upper/lower/mixed case.

For the MM format code, valid month values are: 01, 02,…12.

For the RM format code to represent a Roman numeral month, valid month values are I to XII; with I for January, II for February, and so on.

Examples — Table Data

We start off with an example using table data that does not generate the error. It is always helpful to first find which date format is in effect.

select * from nls_session_parameters where parameter = ‘NLS_DATE_FORMAT’;

Session altered.

PARAMETER                    

——————————

VALUE                                                          

—————————————————————-

NLS_DATE_FORMAT              

DD/MM/RR

Run an SQL query to select data from the SH.PROMOTIONS table (provided by default in most database versions). The PROMO_BEGIN_DATE column value is specified in a valid format as ’01/Jan/00′.

SELECT PROMO_ID, PROMO_NAME, PROMO_END_DATE FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’01/Jan/00′

This example does not generate any error, and lists output:

PROMO_ID PROMO_NAME                     PROMO_END_DATE

———- —————————— —————

       999 NO PROMOTION #                 01/01/99    

       49 TV promotion #12-49           10/09/00    

       42 TV promotion #13-42           22/01/01    

       82 TV promotion #13-82           06/01/01    

       101 TV promotion #12-101           17/09/00    

       141 TV promotion #12-141           23/09/00    

       172 TV promotion #12-172           26/06/00

The SQL command and output are shown in Toad for Oracle in Figure 1.

 

Figure 1. SQL Query with a valid Month value to produce an output

Month value not in valid range

Next, we demonstrate with an example how the “not a valid month” error could get generated if the numeric month value is out of range of 01 to 12. Change the date format to ‘DD-MM-YY’ with command:

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MM-YY’;

Run a SQL query to select table data with a month value that is not a valid month value, such as a month value of 16. This time, the ORA-01843: not a valid month error is generated:

SELECT PROMO_ID, PROMO_NAME, PROMO_END_DATE FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’01-16-00′ >> SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’01-16-00′

*

Error at line 2

ORA-01843: not a valid month

The SQL command, and error message are shown in Toad for Oracle in Figure 2.

 

Figure 2. SQL Query with a month value that is out of valid range to produce a “not a valid month” error

The “not a valid month” error can be fixed easily by specifying a month value that is in the range of 01 to 12, as in query:

SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’01-01-00

As Figure 3 shows an error is not generated, and instead an SQL query output is listed.

Figure 3. SQL Query with a month value that is valid

Month name misspelled

If the month name is misspelled, the “not a valid month” Oracle database error gets generated. As an example, set the month format code to ‘DD-MM-YY’.

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MM-YY’;

Run an SQL query with the month “Jan” misspelled as “Ja,” as in example:

SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’30-Ja-00′

The “not a valid month” error is output:

>> SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’30-Ja-00′

*

Error at line 4

ORA-01843: not a valid month

The SQL command and error output are shown in Figure 4.

Figure 4. SQL Query with a month value that is misspelled

The “not a valid month” error can be fixed easily by spelling the month correctly:

SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’30-January-00′

This time an error is not generated, as indicated by query output shown in Figure 5.

 

Figure 5. SQL Query with a month value that is spelled correctly

Month value not in the proper format

Another reason for the “not a valid month” error is that it is specifying a month format that is not valid. As an example, set the date format to ‘Month-DD-YYYY’.

ALTER SESSION SET NLS_DATE_FORMAT = ‘Month-DD-YYYY’;

Run the following SQL query in which the month value is a numeric value of 01, whereas the date format requires a character value.

SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’01-01-2000′

The “not a valid month” Oracle database error gets generated:

>> SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ’01-01-2000′

*

Error at line 3

ORA-01843: not a valid month

The SQL query with a month value that is not in the required format, and the error output are shown in Figure 6.

Figure 6. SQL Query with a month value that is not in the proper format

To fix the error, specify a month value that is valid such as “January”. Some conversion is built-in, and a value of “Jan” is also valid for the Month format code.

SELECT PROMO_ID, PROMO_NAME FROM SH.PROMOTIONS WHERE PROMO_BEGIN_DATE > ‘Jan-01-2000’

As the output in Figure 7 shows an error is not generated, and instead the query output is listed.

 

Figure 7. SQL Query with a month value that is in the proper format

TO_DATE function

In the following section and subsections we shall be using the TO_DATE function that converts a char value provided as the first argument to a DATE value. Also, optionally a format for the input date value may be provided as the second function, and a language of the text string that is to be converted to a DATE value may be provided as the third function argument.  

We start off with an example that does not generate an error. It is always helpful to find/set the default date format, which is used for the output regardless of the input date format specified in the TO_DATE function call.

ALTER SESSION SET NLS_DATE_FORMAT = ‘MM-DD-YYYY’;

Run the following SQL query that includes a valid format input for the Date value:

SELECT TO_DATE(

   ‘Jan 22 2022’,

   ‘Mon dd, YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL;

The SQL query output is as follows:

TO_DATE(‘JAN222022′,’MONDD,YYYY’,’NLS_DATE_LANGUAGE=AMERICAN’)

—————————————————————

01-22-2022  

The SQL query and output are shown in Figure 8.

 

Figure 8. SQL Query with a valid date and month value in a TO_DATE function call

Subsequent examples demonstrate why the “not a valid month” error could get generated.

Month value out of valid range

For numeric month format, if a month value is out of range i.e., less than 01 or greater than 12 , the “not a valid month” Oracle database error is generated. As an example, run the following SQL query:

SELECT TO_DATE(’16/02/2022′,

   ‘MM/DD/YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL;

The “not a valid month“ error is generated:

>> SELECT TO_DATE(’16/02/2022′,

   ‘MM/DD/YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL

*

Error at line 1

ORA-01843: not a valid month

The SQL query and the error generated are shown in Figure 9.

 

Figure 9. SQL Query with an out of range month generates the “not a valid month” error

To fix the error, specify a numeric month value that is in the range of 01 to 12, such as 02, as shown in the SQL query:

SELECT TO_DATE(’02/02/2022′,

   ‘MM/DD/YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL;

This time, the SQL query generates an output that is not an error condition as shown in Figure 10.

Figure 10. SQL Query with a valid numeric month value

Month name misspelled

If the month name in the TO_DATE function call is misspelled, again the “not a valid month” error is generated. As an example, run the following SQL query where “February” is misspelled:

SELECT TO_DATE(‘Feburary 22 2022’,

   ‘Month dd, YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL;

The error message is:

>> SELECT TO_DATE(

   ‘Feburary 22 2022’,

   ‘Month dd, YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL

*

Error at line 2

ORA-01843: not a valid month

The error for the preceding SQL query is shown in Figure 11.

 

Figure 11. SQL Query with a month value that is misspelled

To fix the error, fix the misspelling:

SELECT TO_DATE(‘February 22 2022’,

   ‘Month dd, YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL;

As shown in Figure 12, this time the SQL query generates an output instead of an error.

 

Figure 12. SQL Query with the month value with the misspelling fixed

The correct spellings are based on the date language. To demonstrate, to use French spelling, set the date language to French:

SELECT TO_DATE(‘février 01 2022’,

   ‘Month dd, YYYY’,

     ‘NLS_DATE_LANGUAGE = FRENCH’)

     FROM DUAL;

TO_DATE(‘FÉVRIER012022′,’MONTHDD,YYYY’,’NLS_DATE_LANGUAGE=FRENCH’)

——————————————————————

02-01-2022  

The preceding SQL query, and its output are shown in Figure 13.

 

Figure 13. SQL Query with the date language as French

Month value not in the proper format

Another reason for the “not a valid month” Oracle database error is the format for the month value being incorrect. As an example, set the date format to ‘Month DD, YYYY’, but provide the month value as a numeric value as in the example:

SELECT TO_DATE(

   ’02/02/2022′,

   ‘Month DD, YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL

The error gets generated:

>> SELECT TO_DATE(

   ’02/02/2022′,

   ‘Month DD, YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL

*

Error at line 2

ORA-01843: not a valid month

The preceding SQL query and the error are shown in Figure 14.

 

Figure 14. SQL Query with a numeric month value when the month format expects character value

The error can be fixed by specifying a month value that matches the month format code. For the preceding example, the error is fixed as:

SELECT TO_DATE(

   ‘January/02/2022’,

   ‘Month/DD/YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL

Fixing error with a default Date value

Another method to fix the “not a valid month” error is to provide a default return value if the error occurs, using the DEFAULT return_value ON CONVERSION ERROR clause. As an example:

SELECT TO_DATE(‘Ja 22, 2022’

       DEFAULT ‘January 01, 2022’ ON CONVERSION ERROR,

       ‘Month dd, YYYY’) «Date»

FROM DUAL;

This time, the error is not generated even though the error condition exists, and the return value is the default return value in the date format set by the NLS_DATE_FORMAT:

Date    

———-

01-01-2022

The preceding SQL query and output are shown in Figure 15.

Figure 15. SQL Query with a default return value on conversion error

Built-in Conversion

Some implicit conversion of the DATE value, including the month value is built-in, or implicit. The implicit conversion could be different for different database versions. As an example a “MON” format value such as “Jan” is considered valid when the month format code is “MONTH,” which would make the valid month value as “January,” as in the example SQL query:

SELECT TO_DATE(

   ‘Jan 22 2022’,

   ‘Month dd, YYYY’,

     ‘NLS_DATE_LANGUAGE = American’)

     FROM DUAL;

In this article we discussed the “not a valid month” Oracle database error; when it could get generated and how to fix it.

Related Links

Debugging PL/SQL Code with Toad for Oracle

How to use the Toad® SQL Tracker to troubleshoot issues

Getting to Know the Three Methods of Connecting to the Database Using Toad for Oracle: TNS, Direct and LDAP

Have questions about Toad Developer Tools? Click Start Discussion and this blog topic will be transferred to the Toad World Forums.

Tags:

Toad for Oracle

sql optimization

not a valid month error

Written by Deepak Vohra

Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.

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.

ORA-01843

ORA-01843: неправильный месяц

Причина:

В дате указан неправильный месяц. Правильные месяцы: Январь — Декабрь (для кода формата MONTH), Янв-Дек (для кода формата MON).

Действие:

Введите правильное значение месяца в правильном формате.

Today, I’d like to write about a tiny little issue – which can cause some real trouble in Oracle 19c. This blog post is about the Pitfall: ORA-1843 – NOT A VALID MONTH in Oracle 19.4 – 19.8.

The Testcase

My testcase is super-simple – and you can reproduce it even without any object in 2 seconds (one for “copy“, the other for “paste“). Simply execute this query:

select to_date('20191120','RRMMDD') from dual;

Or this one where I replace “RR” with the more common “YY”:

select to_date('20191120','YYMMDD') from dual;

Result in Oracle 11.2, 12.1, 12.2.0.1 and 18c

I do this test in 12.2.0.1 – and the expected result is:

SQL*Plus: Release 12.2.0.1.0 Production on Thu Aug 20 10:01:18 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select to_date('20191120','RRMMDD') from dual;

TO_DATE('20191120'
------------------
20-NOV-19

SQL> select to_date('20191120','YYMMDD') from dual;

TO_DATE('20191120'
------------------
20-NOV-19

Everything ok.

Result in Oracle 19.4, 19.5, 19.6, 19.7 and 19.8

And now I repeat the same test in Oracle 19c, in my case in 19.8.0:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 20 10:37:41 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

SQL> select to_date('20191120','RRMMDD') from dual;
select to_date('20191120','RRMMDD') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('20191120','YYMMDD') from dual;
select to_date('20191120','YYMMDD') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month

Ups.

Not good. And not expected.

Background Information

When you search in MOS with “ORA-1843 not a valid month to_date“, the first result is this note:

• MOS Note: 2656012.1 – ORA-1843 Occurs When Executing TO_DATE(‘20200101′,’RRMMDD’) or TO_DATE(‘20200101′,’YYMMDD’)

And there you will find the bug number as well – but the bug is non-public, hence, no link added:

• Bug 30565805 – ORA-01843: NOT A VALID MONTH ERROR WHEN EXECUTING TO_DATE(‘20191120′,’RRMMDD’)

The issue got introduced as a regression of the fix for bug 28509578.

Solution

What I dislike quite a bit in recent MOS notes is this sentence as proposed solution:

Please apply Patch 30565805.
or
Upgrade to Oracle Database 20c.

At first, Oracle 20c is not available on-prem. And furthermore, the release notes for Oracle 20c tell you in the header on page 1:

Oracle Database 20c is available only for preview. It is not available for production use. Upgrades to or from Oracle Database 20c are not supported.

So you can’t upgrade to it, even if it would be available.

But the important message for you is:

• There are one-off fixes available for every 19c RU, and even for some RURs
• The fix will be included in the 19.9.0 October Release Update

More Information and Links

• MOS Note: 2656012.1 – ORA-1843 Occurs When Executing TO_DATE(‘20200101′,’RRMMDD’) or TO_DATE(‘20200101′,’YYMMDD’)
• One-Off Patch 30565805
• Release Notes Oracle 20c

–Mike

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.

---

«ORA-01843: not a valid month» is a very common exception which we all might have faced while working with Dates in Oracle Database. ORA-01843 occurs due to implicit date conversion which is Basics yet Important feature of Oracle Database. With this blog I am trying to explain the reason of ORA-01843 and how we can avoid it.

Following is the EMP table in SCOTT schema.

SQL> select * from emp;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81    5000.15                    10
      7698 BLAKE      MANAGER         7839 01-MAY-81     2850.3                    30
      7782 CLARK      MANAGER         7839 09-JUN-81    2450.45                    10
      7566 JONES      MANAGER         7839 02-APR-81     2975.5                    20
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7902 FORD       ANALYST         7566 03-DEC-81     3000.6                    20
      7369 SMITH      CLERK           7902 17-DEC-80     800.75                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81     1600.9        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81    1250.05       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81    1500.95          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20
      7900 JAMES      CLERK           7698 03-DEC-81     950.99                    30
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

In my database, If I try to compare the HIREDATE with a string in either MM/DD/YYYY or DD/MM/YYYY format, it fails with ORA-01843

SQL> select * from emp where hiredate > '31/12/1985';
select * from emp where hiredate > '31/12/1985'
                                   *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select * from emp where hiredate > '12/31/1985';
select * from emp where hiredate > '12/31/1985'
                                   *
ERROR at line 1:
ORA-01843: not a valid month

In above examples Oracle Database tried implicit date conversion, which is only successful when String values are in default date format. We can find the default date format of our Oracle database using NLS_DATE_FORMAT —

SQL> select sys_context ('USERENV', 'NLS_DATE_FORMAT') from dual;

SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
---------------------------------------------------
DD-MON-RR

Now if we try to compare the HIREDATE with a string in DD-MON-RR (default) format, it should work.

SQL> select * from emp where hiredate > '31-Dec-85';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20

We should remember that Default date format might get changed, so we should be never dependent on implicit date conversion, and convert very string with date value using TO_DATE function.

SQL> select * from emp where hiredate > to_date('12/31/1985','mm/dd/yyyy');

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 19-APR-87    3000.55                    20
      7876 ADAMS      CLERK           7788 23-MAY-87    1100.01                    20

At last, I wanted to add, we can also change NLS_DATE_FORMAT at session level —

SQL> alter session set NLS_DATE_FORMAT = 'mm/dd/yyyy';

SQL> select * from emp where hiredate > '12/31/1985';

     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7788 SCOTT      ANALYST         7566 04/19/1987    3000.55                    20
      7876 ADAMS      CLERK           7788 05/23/1987    1100.01                    20

As you might have noticed, by setting NLS_DATE_FORMAT, we can not only control the implicit conversion while running our SQL, but the output of DATE values is also as per NLS_DATE_FORMAT. I use above trick while debugging only, but when I am writing database code or sql, I always use TO_DATE

I hope you have enjoyed reading this article. Please do post your comments.

Related Posts —
— ORA-01830 date format picture ends before converting entire input string
— Why do I Prefer Oracle Native Date Arithmetic over ANSI INTERVAL
— Playing With Truncate and Date in Oracle Database
— Oracle: Some Important Date Queries
— Oracle SQL: Date Difference in Days, Months and Year

Error ORA-01843 occurs when the user specifies a date that is not a valid calendar month. Valid months are: January, February, March, April, May, June, July, August, September, October, November, and December for format code MONTH. For the MON format code, valid month values are: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, and Dec.

To correct error ORA-01843, you must find the error and enter a valid month value in its correct form.

There are mainly two reasons the user sees this error. The first reason is that the incorrect NLS_DATE_FORMAT is being used. This will often occur when you are working with data from the United States where the commonly used syntax order is mm/dd/yyyy. However, the proper format should be the international standard in which the date precedes the month. Another reason may be that you are attempting to insert a written month name but use a numeric month in the mask instead.

You may consider using the TO_DATE function to specify the date format mask. This function will convert a string to a date. The format for the TO_DATE function is:

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

The following is an example:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(15/07/99, ‘DD/MM/YY’);

This can also be written as:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(071599, ‘MMDDYY’);

The same example can be written yet another way:

SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE(1999/07/15, ‘YYYY/MM/DD’);

All result in a date value of July 15, 1999.

Another option is that you can change the session’s date format with the following line of code. Users should be careful with choosing this option as it may have consequences for other SQL. It may be best to find the specific point of the code and correct the syntax of the month if this is not a frequent occurrence.

ALTER session set NLS_DATE_FORMAT=’DD/MM/YYYY’;

To avoid seeing error ORA-01843, be sure to write valid values for months. It is important to note that error ORA-01843 may not be thrown even when there is an error in month. Therefore, it is even more important to be aware of proper month values that are valid. Make sure that you are writing dates in correct NLS_DATE_FORMAT, especially if data is from the United States in which the standard differs from the worldwide standard practiced in other countries.