Oracle insert failure : not a valid month

Question

I'm trying to import data from an .xlsx spreadsheet which contains date columns. In those columns, dates are displayed in the DD-MON-YY format (for example : 20-AUG-12).

When I'm running the import wizard, everything goes fine until I must precise the columns/fields mapping. I've got a disclaimer saying that the chosen format is not matching the table field definition (my field is a date field). An example of the insert script :

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'm trying to run this query, I got the following error :

ORA-01843: not a valid month 01843. 00000 - "not a valid month"

As you can see, the formats are matching and I can't figure out where the problem is... Any idea guys?

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

Answer 1

Although you are using to_date and correct format model to explicitly convert into date, still getting ORA-01843 it seems like the issue is with the 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 encounter the error with improper NLS_DATE_LANGUAGE. Let's set it to 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 OP's edited question -

My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?

From the documentation, following are the ways to set NLS parameters. Note the order of precedence.

Answer 2

Try to specify the NLS_DATE_LANGUAGE as a paremeter for the TO_DATE function.

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 the alter session if you are going to execute a few queries.