I have a problem when try to select data from a table filtering by date.
For example:
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = '23/04/49';
The Oracle Error is:
Informe de error: Error SQL: ORA-01843: mes no válido 01843. 00000 - "not a valid month" *Cause: *Action:
Probably the source data of table is corrupted, in this case:
- How can i solve this problem?
- Can I change this dates for null?
The results of this select, select * from nls_session_parameters;
, is:
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE SPANISH
NLS_TERRITORY SPAIN
NLS_CURRENCY ¿
NLS_ISO_CURRENCY SPAIN
NLS_NUMERIC_CHARACTERS ,.
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD/MM/RR
NLS_DATE_LANGUAGE SPANISH
NLS_SORT SPANISH
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD/MM/RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY ¿
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
asked Jan 16, 2014 at 8:56
2
You should use the to_date
function (oracle/functions/to_date.php
)
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE('23/04/49', 'DD/MM/YY');
answered Jan 16, 2014 at 9:01
8
You are comparing a date column to a string literal. In such a case, Oracle attempts to convert your literal to a date, using the default date format.
It’s a bad practice to rely on such a behavior, as this default may change if the DBA changes some configuration, Oracle breaks something in a future revision, etc.
Instead, you should always explicitly convert your literal to a date and state the format you’re using:
SELECT * FROM MYTABLE WHERE MYTABLE.DATEIN = TO_DATE('23/04/49','MM/DD/YY');
answered Jan 16, 2014 at 9:03
MureinikMureinik
295k52 gold badges302 silver badges346 bronze badges
2
If you don’t need to check exact timestamp, use
SELECT * FROM MYTABLE WHERE trunc(DATEIN) = TO_DATE('23-04-49','DD-MM-YY');
otherwise, you can use
SELECT * FROM MYTABLE WHERE DATEIN = TO_DATE('23-04-49 20:18:07','DD-MM-YY HH24:MI:SS');
Here, you use hard code date,if you directly compare then you must use DD-MM-YY HH24:MI:SS else you might get ORA-01849: hour must be between 1 and 12.
answered Sep 14, 2017 at 6:43
I know this is a bit late, but I’m having a similar issue. SQL*Plus
executes the query successfully, but Oracle SQL Developer
shows the ORA-01843: not a valid month error.
SQL*Plus
seems to know that the date I’m using is in the valid format, whereas Oracle SQL Developer needs to be told explicitly what format my date is in.
-
SQL*Plus statement
:select count(*) from some_table where DATE_TIME_CREATED < '09-12-23';
VS
-
Oracle SQL Developer statement
:select count(*) from some_table where DATE_TIME_CREATED < TO_DATE('09-12-23','RR-MM-DD');
answered Aug 17, 2016 at 19:48
Just in case this helps, I solved this by checking the server date format:
SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';
then by using the following comparison (the left field is a date+time):
AND EV_DTTM >= ('01-DEC-16')
I was trying this with TO_DATE
but kept getting an error. But when I matched my string with the NLS_DATE_FORMAT
and removed TO_DATE
, it worked…
zx485
28.1k28 gold badges50 silver badges59 bronze badges
answered Jan 4, 2017 at 22:51
In a comment to one of the answers you mention that to_date with a format doesn’t help. In another comment you explain that the table is accessed via DBLINK.
So obviously the other system contains an invalid date that Oracle cannot accept. Fix this in the other dbms (or whatever you dblink to) and your query will work.
Having said this, I agree with the others: always use to_date with a format to convert a string literal to a date. Also never use only two digits for a year. For example ’23/04/49′ means 2049 in your system (format RR), but it confuses the reader (as you see from the answers suggesting a format with YY).
answered Jan 16, 2014 at 11:13
Thorsten KettnerThorsten Kettner
87.8k7 gold badges47 silver badges71 bronze badges
3
If the source date contains minutes and seconds part, your date comparison will fail.
you need to convert source date to the required format using to_char and the target date also.
answered Jul 6, 2014 at 12:52
If you are using command line tools, then you can also set it in the shell.
On linux, with a sh type shell, you can do for example:
export NLS_TIMESTAMP_FORMAT='DD/MON/RR HH24:MI:SSXFF'
Then you can use the command line tools and it will use the specified format:
/path/to/dbhome_1/bin/sqlldr user/pass@host:port/service control=table.ctl direct=true
answered Aug 1, 2020 at 17:26
Try using:
SELECT *
FROM MYTABLE
WHERE MYTABLE.DATEIN is not null
AND MYTABLE.DATEIN = '23/04/49';
answered Mar 31, 2021 at 12:09
0
Use the month
as a string.
Example:
(12-Apr-2002) or (12-April-2002)
haydnD
2,2255 gold badges26 silver badges61 bronze badges
answered Mar 31, 2021 at 7:21
SaranSaran
211 bronze badge
0
Although the answers using TO_DATE are correct, I prefer to use the ANSI SQL format for dates:
DATEIN = DATE '1949-04-23'
It works in Oracle and other DBMS ANSI SQL compliant. This is specially important if your application is DBMS agnostic.
answered Aug 3, 2021 at 16:42
Lluis MartinezLluis Martinez
1,9548 gold badges28 silver badges42 bronze badges
Try alter session set NLS_DATE_FORMAT=’DD/MM/YY’; — or whatever format you want
I faced the same problem, on PROD, all the code were already in this format, but on preprod, it’s not set,
So this means you change the default date format used by oracle
answered Jan 9 at 8:12
ALTER session set NLS_LANGUAGE=’AMERICAN’;
answered Apr 13, 2020 at 17:33
2
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:
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
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 4th 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»
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.
I’m trying to insert a row in oracle database and when i try with the date i have the errore in title.
My date is DD/MM/YYYY HH:MM:SS
(example 25/01/2013 12:07:19
)
edit for better explain:
i have an android application and want to insert a row in oracle database by php.
in Php i have:
$sqlString = sprintf("INSERT INTO GUASTI (%s, %s, %s, %s, %s, %s)
VALUES ('%s', '%s', '%s', '%s', '%s', '%s')"
,'guasto','tipo','data', 'localita', 'indirizzo_id', 'utente_id',
$array['guasto'], $array['tipo'], $array['data'], $array['localita'], $idUtenti, $idIndirizzo);
where $array['data']
is 25/01/2013 12:07:19
p.s. i know that there are security problems there but it is not a problem for now.
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.
Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.
The Verdict
There are several errors users may get while using Oracle database. ORA-01843: not a valid month error is one of them. Here, I have tried my best to provide you the ways to know how to resolve not a valid month error in oracle. All these are very easy to apply and to apply these solutions, you just need some basic knowledge of Oracle database. So, try these fixes and easily get your oracle database file back in no time.
Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.
ORA-01843
ORA-01843: неправильный месяц
Причина:
В дате указан неправильный месяц. Правильные месяцы: Январь — Декабрь (для кода формата MONTH), Янв-Дек (для кода формата MON).
Действие:
Введите правильное значение месяца в правильном формате.