Oracle Insert Failure:Not a Valid Month

Oracle insert failure : not a valid month

Although you are using to_date and correct format model to explicitly convert into date, still getting ORA-01843 it seems like the issue is with the NLS_DATE_LANGUAGE. Read more about it here.

SQL> DROP TABLE t PURGE;

Table dropped.

SQL> CREATE TABLE t(A DATE);

Table created.

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='FRENCH';

Session altered.

SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE';

PARAMETER            VALUE
-------------------- ----------
NLS_DATE_LANGUAGE    FRENCH

SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'));
INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'))
                             *
ERROR at line 1:
ORA-01843: not a valid month

So, I encounter the error with improper NLS_DATE_LANGUAGE. Let's set it to AMERICAN.

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

Session altered.

SQL> SELECT * FROM nls_session_parameters WHERE parameter = 'NLS_DATE_LANGUAGE';

PARAMETER            VALUE
-------------------- ----------
NLS_DATE_LANGUAGE    AMERICAN

SQL> INSERT INTO t VALUES(to_date('01-jan-2012','dd-mon-yyyy'));

1 row created.

SQL>

UPDATE To OP's edited question -

My date language paramater is set to "FRENCH". Can I change it to "AMERICAN" ?

From the documentation, following are the ways to set NLS parameters. Note the order of precedence.

ORA-01843: not a valid month when insert a date in oracle

MM is for month. Use MI for minutes.

You have

HH:MM:SS

every time where the minutes are greater than 12 will trigger the error as you are telling Oracle to interpret them as months.

You are also using HH without am/pm (in your example you just used 12). If you are using a 24 format use HH24

DD/MM/YYYY HH24:MI:SS

or if you want the 12-hour format

DD/MM/YYYY HH:MI:SSAM

and then

02/01/2013 07:42:00am

Edit

You are inserting the date with the default format which is MM/DD/YYYY (american standard): 25 is not a valid month. You can use the TO_DATE function

'TO_DATE(' . $array['data'] . ', DD/MM/YYYY HH24:MI:SS)'

ORA-01843: not a valid month when executing insert statement

MON is the format mask for the name of the month. As you are supplying the number, you need MM

to_timestamp('2018-07-02 08:03:24.466381 AM', 'yyyy-MM-dd hh.mi.ssxff am')

And as you are supplying a four-digit year yyyy is probably a better choice than rr

ORA-01843: not a valid month when inserting a date

Use TO_DATE('2021/07/02', 'YYYY/MM/DD') to convert your date values to the standard database date format

Replace the above date value as your date columns

SQL Error: ORA-01843: not a valid month

Your column in the table is defined as

Date_done     Date,

You are storing the date into a VARCHAR2 variable

V_DATE  VARCHAR(30);
....
SELECT EVENTLOG_ID_SEQ.NEXTVAL, USER, 
TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS') INTO V_LOGID, V_USER, V_DATE
...

And you are inserting this variable into the DATE column.

...
VALUES (V_LOGID, V_USER, V_DATE, 'INSERT');

Keep datatype consistent to avoid confusion.

Not a valid month while inserting data in oracle

you should cast datatype of your date columns by

to_date('29-09-2016 17:10:19', 'DD-MM-YYYY HH24:MI:SS')

Not a valid month on an INSERT statement

Unless you have a trigger on the table that is setting a date or timestamp column, which would give some indication in the full error stack, it sounds like your NLS_DATE_LANGUAGE is not expecting an English-language month abbreviation.

What you have is valid in English:

alter session set nls_timestamp_format = 'RR/MM/DD HH24:MI:SSXFF';
alter session set nls_date_language ='ENGLISH';

select to_timestamp('15-APR-14 01.36.58.803000000 PM',
  'DD-MON-RR HH.MI.SS.FF AM') as my_date
from dual;

MY_DATE                   
---------------------------
14/04/15 13:36:58.803000000 

But if your session's default date language is Polish (guessing from your profile) it will give this error - with the error message still in English:

alter session set nls_date_language ='POLISH';

select to_timestamp('15-APR-14 01.36.58.803000000 PM',
  'DD-MON-RR HH.MI.SS.FF AM') as my_date
from dual;

SQL Error: ORA-01843: not a valid month
01843. 00000 -  "not a valid month"

If you don't want to set your session to English you can override that for a specific statement by giving the optional third parameter to to_timestamp():

alter session set nls_date_language ='POLISH';

select to_timestamp('15-APR-14 01.36.58.803000000 PM',
  'DD-MON-RR HH.MI.SS.FF AM',
  'NLS_DATE_LANGUAGE=ENGLISH') as my_date
from dual;

MY_DATE                   
---------------------------
14/04/15 13:36:58.803000000 

You can also avoid the issue entirely by using month numbers instead of month names, or using the ANSI timestamp literal syntax:

select timestamp '2014-04-15 13:36:58.803' from dual;

TIMESTAMP'2014-04-1513:36:58.803'
---------------------------------
14/04/15 13:36:58.803000000       

These methods also all work for date columns; the to_date() function is affected by NLS settings in the same way and has the same optional date language parameter.

ORA-01843: not a valid month error Oracle SQL

The error implies that there is at least one row in the table where the value in open_date_txt cannot be converted to a DATE using the given format string.

The query will run until it hits that row, then throw the error. Of course, you never see the row that causes the error in your output, so it is hard to find the value causing the problem.

One technique is to go through row by row and raise a more useful exception that identifies the entry causing the error. For example:

DECLARE
  d DATE;
BEGIN
  FOR x IN (SELECT name, open_date_txt FROM table) LOOP
    BEGIN
      d := TO_DATE( x.open_date_txt, 'yyyy-mm-dd');
    EXCEPTION
      WHEN others THEN
        raise_application_error(-20000, 'Error on name: ' || x.name, TRUE );
    END;
  END LOOP;
END;

You could also modify this to print out the name for the row and continue, so you'd find if there are multiple rows with bad data.

Oracle insert query returned error 01843. 00000 - not a valid month

Oracle will not automatically convert dd-mm-yyyy or mm-dd-yyyy to a date. You need to explicitly use to_date(). Although, it is a good idea to include column names in an insert. Something like:

insert into X_VW_PRICEPRODUCT_IC(col1, . . . )
    SELECT col1, . . ., to_date(datecol, 'MM-DD-YYYY), . . .
    FROM ER_PRICEPRODUCT@link;

---

Related Topics