Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ORA-01843: not a valid month : TO_DATE('12-JUN-02','DD-MON-YY')

Tags:

sql

oracle

I've faced a problem during insert. Here is the problem:

INSERT INTO SALES_ORDER_A4 (ORDERNO , CLIENTNO , ORDERDATE , DELYADDR , SALESMANNO , DELYTYPE , BILLYN , DELYDATE, ORDERSTATUS)
SELECT 'O19008' ,
       'C00005' ,
       to_date('24-MAY-02','DD-MON-YY'),
       'Delhi' ,
       'S00004' ,
       'F' ,
       'N' ,
       to_date('26-MAY-02','DD-MON-YY') ,
       'In Process'
FROM DUAL
UNION ALL
SELECT 'O19001' ,
       'C00001' ,
       to_date('12-JUN-02','DD-MON-YY') ,
       'Delhi' ,
       'S00001' ,
       'F' ,
       'N' ,
       to_date('20-JUN-02','DD-MON-YY') ,
       'In Process'
FROM DUAL
UNION ALL
SELECT 'O19002' ,
       'C00002' ,
       to_date('25-JUN-02','DD-MON-YY'),
       'Delhi' ,
       'S00002' ,
       'P' ,
       'N' ,
       to_date('27-JUL-02','DD-MON-YY') ,
       'Cancelled'
FROM DUAL

I have faced an exception at phrase *to_date('12-JUN-02','DD-MON-YY' )* that:

ORA-01843: not a valid month

My instructor can not find the problem, what is the problem here?

like image 368
tahasozgen2 Avatar asked Feb 25 '14 09:02

tahasozgen2


People also ask

How do I fix Ora 01843 Not valid month?

It may be best to find the specific point of the code and correct the syntax of the month if this is not a frequent occurrence. ALTER session set NLS_DATE_FORMAT='DD/MM/YYYY'; To avoid seeing error ORA-01843, be sure to write valid values for months.

How do I fix not valid month in Oracle?

To fix the error, specify a month value that is valid such as “January”. Some conversion is built-in, and a value of “Jan” is also valid for the Month format code.

What is To_date in SQL?

The TO_DATE function converts date strings in various formats to a date integer value, with data type DATE. It is used to input dates in various string formats, storing them in a standard internal representation. TO_DATE returns a date with the following format: nnnnn.


1 Answers

This is mostly because NLS_DATE_LANGUAGE is not set correctly.

First check the value by:

select * from v$nls_parameters where parameter like '%DATE%'

If the value of NLS_DATE_LANGUAGE is not American then change it by:

alter session set nls_date_language='American';

And try again.

like image 77
xdazz Avatar answered Oct 19 '22 10:10

xdazz