ORA-01843: Not a valid month
First determine what date format to use:
SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT'; PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_DATE_FORMAT DD-MON-RRWe can see that we need to use first a day followed by a month using the 3 letter abbreviation for the month, followed by a year:
SQL> select to_date('01-13-06') from dual;
select to_date('01-13-06') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> select to_date('01-12-06') from dual;
select to_date('01-12-06') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> select to_date('01-DEC-06') from dual;
TO_DATE('
---------
01-DEC-06
Only by passing a valid month to the function or column, we can solve this problem.Alternatively we can change the value of the NLS_DATE_FORMAT parameter to reflect the dates we are passing in:
SQL> alter session set nls_date_format='DD-MM-RR';
Session altered.
SQL> select to_date('01-13-06') from dual;
select to_date('01-13-06') from dual
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> select to_date('01-12-06') from dual;
TO_DATE(
--------
01-12-06
Which can lead to some surprising behaviour:
SQL> select to_date('01-DEC-06') from dual;
TO_DATE(
--------
01-12-06
This occurs because Oracle tries a few conversion formats. See the Oracle functions manual for more details about this.You can block this behaviour by using the FX format:
SQL> alter session set nls_date_format='FXDD-MM-RR';
Session altered.
SQL> select to_date('01-12-06') from dual;
TO_DATE(
--------
01-12-06
SQL> select to_date('01-DEC-06') from dual;
select to_date('01-DEC-06') from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Welcome to our forum for Oracle error: ORA-01843 Add your own message
the date format in the textfile is 'mm/dd/yyyy hh24:mi' and i have changed the default format to this format and i am trying to insert the date value. its raising the error. can u please help me?
alter session set nls_date_format='mm/dd/yyyy hh24:mi';
Hi
I have an interface managed by a CTL file. The interface is between the data source and the Datawarehouse.
Some days ago a date field of the source table is changed from mm-dd-yyyy to the new format yyyy-mm-dd
in the CTL file I have this field:
...
last_update_date,
...
The problem is that when the database returns: ORA-01843: not a valid month
The solution that I have to implement is manage the data conversion in the CTL file in order to provide to the datawarehouse the date in the correct format (mm-dd-yyyy). But I don't know what I have to add in my CTL File.
can you help me?
You can specify the format mask in the control file:
DATECOLUMN POSITION(1:10) DATE "YYYY-MM-DD"
do I have to put this in CTL file ?
...
last_update_date,
...
Please note that the format that I want is mm-dd-yyyy (the old one) and not yyyy-mm-dd
Yes, put it in the loader file:
last_update_date "MM-DD-YYYY"
Hi,
When i try to load date from SQL Server Source to Oracle target i get error like "Not a Valid Month". After setting the NLS_Session_Parameter too i have the same error. Help will be highly usefull for me to proceed further.
Thanks in Advance
Did you try setting the NLS_DATE_FORMAT parameter to the correct format?
CREATE TT_EXT_DATA (APSFQ_APS_UID ), APSFQ_FT_UID ), APSFQ_FI_UID ), APSFQ_SS_UID ), APSFQ_DATE_LOADED , APSFQ_FI_HASH_VALUE ), APSFQ_INSTANCE ) ) external ( oracle_loader ext PARAMETERS ( RECORDS DELIMITED NEWLINE CHARACTERSET WE8MSWIN1252 BYTES BADFILE 'ext.bad' 'ext.txt' FIELDS TERMINATED LDRTRIM MISSING FIELD NULL ( APSFQ_APS_UID , APSFQ_FT_UID ), APSFQ_FI_UID ), APSFQ_SS_UID ), APSFQ_DATE_LOADED MASK "DD-MON-RR" , APSFQ_FI_HASH_VALUE , APSFQ_INSTANCE ) )) location ( 'ext1.txt' ) ) ; I tried to eport data to an external table but everything seems correct but I have this ouput from the logfile ORA-01843: not a valid month can someone help me to solve it ,
TABLE
NUMBER(10
NUMBER(10
NUMBER(10
NUMBER(10
DATE
VARCHAR2(32
NUMBER(10
ORGANIZATION
TYPE
DEFAULT DIRECTORY
ACCESS
BY
STRING SIZES ARE IN
log:
LOGFILE log:
BY "|" OPTIONALLY ENCLOSED BY '"' AND '"'
VALUES ARE
CHAR(255
CHAR(2000
CHAR(255
CHAR(255) DATE_FORMAT date
CHAR(50
REJECT LIMIT unlimited NOPARALLEL
error processing column APSFQ_INSTANCE in row 10 for datafile /usr/app/oracle/product/9.2.0/thotho/ext1.txt
ORA-01722: invalid number
error processing column APSFQ_DATE_LOADED in row 12 for datafile /usr/app/oracle/product/9.2.0/thotho/ext1.txt
ORA-01843: not a valid month
Hi,
when I call a stored procedure with date parameter from a form having text item with date format 'yyyy/mm/dd' I get the error ora-01843 not a valid month .
please reply me how I solve this problem ?
thanks
You need to call your procedure with the correct date format. Probably your NLS_DATE_FORMAT is set differently:
exec myproc(to_date('20100804', 'YYYYMMDD')
Add your message
If you need more information about this particular error message, you can leave a forum message.
We are replying to this message whenever we have some spare time, so please do not consider this as a private 'solve my critical issue asap' service.
Should you need professional Oracle Assistance to make your project a success, please have a look at our consultancy services.
In order to prevent automatic generation of messages, we are asking for a validation code. This code is unique and is generated every time a new message is asked.
If you do not enter the validation correctly, your message will not be recorded.