How to Resolve ORA-01843 Error Message
There might be typos in your statement just like the following example.
]$ 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;
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’)
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.
Oracle 10g, ora-01843, как настроить дату?
Сразу хочу сказать, в оракле я не профессионал, но чтение мануалов и поиск в гугле не помогли.
Проблема — поднял новый сервер oracle 10g, залил данные.
При попытке взаимодействия с данными получаю ошибку — ora-01843 (не валидный месяц).
У данных дата выглядит вот так: 19-DEC-07.
Делаю команду:
ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YY’;
Потом:
SELECT TO_DATE(’20-DEC-07′) FROM TABLE;
Получаю результат
ORA-01843: месяц не верен.
В итоге два вопроса:
1) Какой формат данных должен быть для данной как пример даты?
2) Каким образом записать информацию о формате даты в базу, чтобы после перезагрузки сервера настройка не слетала?
Fixing the «not a valid month» Oracle database error
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:
- The month value is out of the valid range (for numeric month format)
- The month name is misspelled (for character month format)
- 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:
where parameter = ‘NLS_DATE_FORMAT’;
The output lists the default date format as 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:
where parameter = ‘NLS_TERRITORY’;
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:
where parameter = ‘NLS_DATE_LANGUAGE’;
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’;
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:
The SQL query output is as follows:
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:
The “not a valid month“ error is generated:
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:
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’,
The error message is:
‘Feburary 22 2022’,
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’,
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’,
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:
The error gets generated:
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:
Get Toad for Oracle Base Subscription today
Subscription / eStore: buy up to 10 licenses at a time, get auto update, support, announcements/invites to education.
Talk to our professionals: demos, custom solutions, volume discounts.
Not ready to buy? Get Toad for Oracle a 4 th way . try it free for 30 days.
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»
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 :
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:
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.
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.
Oracle / PLSQL: ORA-01843 Error Message
Learn the cause and how to resolve the ORA-01843 error message in Oracle.
Description
When you encounter an ORA-01843 error, the following error message will appear:
- ORA-01843: not a valid month
Cause
You entered a date, but the month portion of the date was not a valid month.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
Re-enter the date value using either a MONTH format mask.
The valid values for month are:
- January
- February
- March
- April
- May
- June
- July
- August
- September
- October
- November
- December
Or you could use the MON format mask and use one of the following values:
- Jan
- Feb
- Mar
- Apr
- May
- Jun
- Jul
- Aug
- Sep
- Oct
- Nov
- Dec
If all else fails, you may want to consider using the TO_DATE function and specifying the date format mask.