sql - 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', 
             '[email protected]'); 

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

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', 
             '[email protected]'); 

Of course, you can change it with the alter session if you are going to execute a few queries.