• Home
• 

• Products
• DbaBar

• Oracle administration made easy

• OraSpy

• Simple Oracle monitoring

• Oracle Locator Express

• The Oracle Home Selector Replacement

• Oracle Password Changer

• Change (expired) passwords

• Oracle Listener Remover

• Remove obsolete Listeners on Windows

• Actions
• Download

• Download Free Products and Trials

• Buy Licenses

• Buy an official license

• Support

• Get support for our products

• Oracle Error Codes

• Oracle Error Messages Explained

• DbMotive
• About Us

• Everything you want to know about us

• News

• Jan 27th: New Oracle Locator Express Release

• Sitemap

• Browse our website

 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 behaviour:


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 behaviour 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