Knowledge Base

Use a valid month for your date

ORA-01843: Not a valid month

You are trying to insert an invalid month into a date column, or passed as an argument at the to_date function.
First determine what date format to use:

SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR

We can see that we need to use first a day followed by a month using the 3 letter abbreviation for the month, followed by a year:

SQL> select to_date('01-13-06') from dual;
select to_date('01-13-06') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('01-12-06') from dual;
select to_date('01-12-06') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('01-DEC-06') from dual;

TO_DATE('
---------
01-DEC-06


Only by passing a valid month to the function or column, we can solve this problem.

Alternatively we can change the value of the NLS_DATE_FORMAT parameter to reflect the dates we are passing in:

SQL> alter session set nls_date_format='DD-MM-RR';

Session altered.

SQL> select to_date('01-13-06') from dual;
select to_date('01-13-06') from dual
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('01-12-06') from dual;

TO_DATE(
--------
01-12-06

Which can lead to some surprising behavior:

SQL> select to_date('01-DEC-06') from dual;

TO_DATE(
--------
01-12-06


This occurs because Oracle tries a few conversion formats. See the Oracle functions manual for more details about this.

You can block this behavior by using the FX format:

SQL> alter session set nls_date_format='FXDD-MM-RR';

Session altered.

SQL> select to_date('01-12-06') from dual;

TO_DATE(
--------
01-12-06

SQL> select to_date('01-DEC-06') from dual;
select to_date('01-DEC-06') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Oracle Gold Partner DbMotive is an Oracle Gold Partner