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

If any of you is encountering an error like "ORA-01843: not a valid month" while running an oracle SQL via any tool such as SQL Developer,TOAD,Oracle Data Integrator, Informatica then the only possibility which is there is that some columns in a table is of VARCHAR datatype which has data values as the date values whose format is different from what you have defined in "NLS_SESSION_PARAMETERS" of the database.

Demonstration:

01. SELECT * FROM NLS_SESSION_PARAMETERS; [Login via SYS to check the values ]

 02. Create a table say "TEST" with one VARCHAR column say the column name "DT"

CREATE TABLE TEST (DT VARCHAR(20));

03.  INSERT a data in the column different from your NLS_DATE_FORMAT.In our case NLS_DATE_FORMAT is 'DD-MON-RR'

INSERT INTO TEST VALUES('31/12/2014');

Clearly we have inserted the data in format other than NLS_DATE_FORMAT.

04. Try Query the "DT" column using the TO_DATE function without specifying any format specifier.You will run into the issue "ORA-01843: not a valid month"

SELECT TO_DATE(DT) FROM TEST;

05. Therefore, You need to specify either the format specifier while querying the VARCHAR column using TO_DATE function or insert the data in a table for the same VARCHAR column in the format what is defined as in NLS_DATE_FORMAT of the database.

SELECT TO_DATE(DT,'DD/MM/YYYY') FROM TEST;

Note : - If in the same "DT" column if you have one data say in "DD/MM/YYYY" format and other data in "MM/DD/YYYY" format then the data values are not consistent and the SQL will fail.Therefore, it is mandatory to insert the data in the same column in consistent format which can be either in "DD/MM/YYYY" or "MM/DD/YYYY" across the data set for the column.