varoluscuprens varoluscuprens -4 years ago 186
SQL Question

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

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?

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download