ORA-01843: not a valid month

ORA-01843: not a valid month

Error
ORA-01843: not a valid month

SQL> insert into emp values (1,'29-12-10');
insert into emp values (1,'29-12-10')
*
ERROR at line 1:
ORA-01843: not a valid month

Cause
Date format is mismatch with the DB setting. you need to check the format with NLS_DATE_FORMAT

Solution
You can check the NLS_DATE_FORMAT first then you have 3 options:
1. Change your data date format.
2. Change NLS_DATE_FORMAT at session level.
3. Use of TO_DATE function in Query.

Change you data date format

--You need to check the NLS_DATE_FORMAT for your database.
SQL> show parameter nls_Date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      DD-MON-RR

-If its not match you can change the data in specific format.
SQL> insert into emp values (1,'29-DEC-10');
1 row created.

Change NLS_DATE_FORMAT at session level

-- Change the NLS_DATE_FORMAT at session level
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YY';
Session altered.
SQL> insert into emp values (1,'29-12-10');
1 row created.

Use TO_DATE function

SQL> insert into emp values (1,To_date('29-12-10','DD-MM-YY'));
1 row created.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.