Inserting date text into table returns `not a valid month`

Question

My NLS_DATE_FORMAT is DD/MM/YY

When I enter values like these

Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,FACNO,CUSTNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) values ('E100',to_date('25/10/13'),to_date('06/06/13'),'F100','C100',to_date('08/06/13'),'Approved',5000,80000,'B1000');

The row is inserted but when I use the following Format this error message appears.

Error starting at line 2 in command:

Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,FACNO,CUSTNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) 
values ('E101',to_date('26-OCT-13','DD-MON-RR'),to_date('28-JUL-13','DD-MON-RR'),'F100','C100',null,'Pending',5000,80000,'B1000')

Error report:

SQL Error: ORA-01843:not a valid month

01843. 00000 - "not a valid month"

*Cause:

*Action:

Can anybody help?

Why have you included tags for mysql and sql-server? You're *only* talking about Oracle, right? — Jon Skeet, Apr 01 '16 at 11:19
SQL Server is not Oracle and Oracle isn't MySQL. In general though, instead of passing localized dates and hope the database guesses the correct format, you should use a standard format like the ISO 8601 date*tiume* format, or the unseparated `YYYYMMDD` format. `YYYY-MM-DD` isn't enough, as there *are* cultures that use similar formats. There *are* differences between database vendors though. What database are you using? — Panagiotis Kanavos, Apr 01 '16 at 11:29
Possible duplicate of [Does Oracle's "date'\[yyyy-mm-dd\]'" literal always use the yyyy-mm-dd pattern?](http://stackoverflow.com/questions/7673683/does-oracles-dateyyyy-mm-dd-literal-always-use-the-yyyy-mm-dd-pattern) — Panagiotis Kanavos, Apr 01 '16 at 11:33
Date **literals** in Oracle are specified as `date'YYYY-MM-DD'` , eg `date'2016-04-01'`. Anything else is prone to failure. The real fix of couse is to use *parameterized queries* — Panagiotis Kanavos, Apr 01 '16 at 11:35
Don't use month _names_. Most probably your NLS settings are not using english and thus `OCT` or `JUL` aren't valid. Use a language independent format or use an ANSI date literal: `date '2013-10-26'` — , Apr 01 '16 at 11:35
sorry for the wrong tags , i didn't gave much attention,i use oracle database 12c and oracle sql developer. the problem might be that my NLS_DATE_LANGUAGE is Greek so it doesn't accept months in English. Could it be possible to change language to English? — Akis, Apr 01 '16 at 11:51
Where are the string values coming from? Can they be supplied as date literals instead? Or in a non-NLS-dependent format? If not, are they *always* supplied with English month abbreviations? — Alex Poole, Apr 01 '16 at 12:16
Possible duplicate of [“Not a valid month” on an INSERT statement](http://stackoverflow.com/q/24990294/266304)? — Alex Poole, Apr 01 '16 at 12:19
Do not switch the NLS language, provide a language-independent date literal — , Apr 01 '16 at 12:28

score 0 · Answer 1 · edited May 23 '17 at 12:15

Exists somewhere else: Oracle insert failure : not a valid month

Maybe if you specify NLS_DATE_LANGUAGE?

Insert into EVENTREQUEST (EVENTNO,DATEHELD,DATEREQ,FACNO,CUSTNO,DATEAUTH,STATUS,ESTCOST,ESTAUDIENCE,BUDNO) 
values ('E101',to_date('26-OCT-13','DD-MON-RR', 'NLS_DATE_LANGUAGE = AMERICAN'),to_date('28-JUL-13','DD-MON-RR', 'NLS_DATE_LANGUAGE = AMERICAN'),'F100','C100',null,'Pending',5000,80000,'B1000')

Inserting date text into table returns `not a valid month`

1 Answers1