Solve ORA-01843: not a valid month error in TO_DATE function

It happens while importing some SQL script to have a ORA-01843: not a valid month error, when you try to execute a TO_DATE function in your script, in order to import a date field.  This is caused by how Oracle interprets the specified date, that is related to localization settings.




To have a look to you localization settings, type:

select *
from nls_session_parameters
where parameter in ('NLS_DATE_FORMAT','NLS_DATE_LANGUAGE');

The result could be something like this, depending on your Oracle locale:

PARAMETER         | VALUE
------------------+-----------
NLS_DATE_FORMAT   | DD-MON-RR
NLS_DATE_LANGUAGE | AMERICAN

In the example the locale is AMERICAN. Now, try for example to import a date in an Italian format:

SELECT to_date('30-GEN-13','DD-MON-RR') FROM DUAL;

The output is the error:

ORA-01843: not a valid month

This is because our NLS_DATE_LANGUAGE was AMERICAN and it does not recognise the 'GEN' month. So we can temporarily change in the current session NLS_LANGUAGE to italian before running the script:

alter session set NLS_LANGUAGE=ITALIAN;
SELECT to_date('30-GEN-13','DD-MON-RR') FROM DUAL;

The output is now our desired date:

2013-01-30 00:00:00

You can change the locale value according to the correct input date format, like SPANISH, GERMAN, and so on.