Oracle Insert Error: Invalid Month

Question

I am trying to import data from a .xlsx table that contains date columns. In these columns, dates are displayed in DD-MON-YY format (ex: 20-AUG-12).

When I start the import wizard, everything goes fine until I refine the mapping of columns and fields. I have a disclaimer saying that the selected format does not match the definition of a table field (my field is a date field). Script insert example:

INSERT INTO simulation (simulation_id, simulation_name, sim_start_date, sim_end_date, status, last_run_date, moddat, modusr, notification_email) VALUES (251.0, 'Proposal Test', To_date('01-DEC-11', 'DD-MON-YY'), To_date('31-DEC-11', 'DD-MON-YY'), 'C', To_date('09-AUG-12', 'DD-MON-YY'), To_date('09-AUG-12', 'DD-MON-YY'), 'Brent Weintz', ' [e-mail] '); 

When I try to run this query, I received the following error:

ORA-01843: Invalid month 01843. 00000 - "Invalid month"

As you can see, the formats are consistent, and I cannot figure out where the problem is ... Any ideas?

EDIT: My date language setting is set to "FRENCH". Can I change it to "AMERICAN"?

Answer 1

Although you use to_date and the correct format model to explicitly convert to a date, still getting ORA-01843 , it seems the problem is with NLS_DATE_LANGUAGE . Read more about it here .

 SQL> DROP TABLE t PURGE; Table dropped. SQL> CREATE TABLE t(A DATE); Table created. SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='FRENCH'; Session altered. SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE'; PARAMETER VALUE -------------------- ---------- NLS_DATE_LANGUAGE FRENCH SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy')); INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy')) * ERROR at line 1: ORA-01843: not a valid month 

So, I meet an error with the wrong NLS_DATE_LANGUAGE . Install it in AMERICAN .

 SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; Session altered. SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE'; PARAMETER VALUE -------------------- ---------- NLS_DATE_LANGUAGE AMERICAN SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy')); 1 row created. SQL> 

UPDATE To editable question -

The date parameter for the date is set to "FRENCH". Can I change it to "AMERICAN"?

From the documentation , the methods for setting NLS parameters follow. Pay attention to the order of priority.

Answer 2

Try specifying NLS_DATE_LANGUAGE as a parameter for TO_DATE .

 INSERT INTO simulation (simulation_id, simulation_name, sim_start_date, sim_end_date, status, last_run_date, moddat, modusr, notification_email) VALUES (251.0, 'Proposal Test', To_date('01-DEC-11', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'), To_date('31-DEC-11', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'), 'C', To_date('09-AUG-12', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'), To_date('09-AUG-12', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN'), 'Brent Weintz', ' [e-mail] '); 

Of course, you can change it with an alter session if you intend to complete several queries.