I'm using Oracle SQL and i have a dates issue.
I got a long script with many Insert
commands such as:
Insert into MyTable
(TIME,CROSS,BID,ASK)
values (to_timestamp('13-NOV-14 03.38.27.785000000 PM','DD-MON-RR HH.MI.SSXFF AM'),'USD/CHF',0.96294,0.963);
However, i'm getting the following error:
ORA-01843 - "not a valid month"
Here is the sysdate format:
22-FEB-15 04.57:18
I tried the following command, and i got a format error:
alter session set nls_date_format = 'DD-MON-RR HH.MI:SSXFF';
How can i fix this issue? In addition, i want to convert these dates to the following format:
DD/MM/YYYY HH24:MI:SS.miliseconds
Can this conversion be in the insert command?
Please advise
It suspect that your locale_specific NLS_DATE_LANGUAGE is different.
Try the following query with NLS_DATE_LANGUAGE parameter included in the to_timestamp:
to_timestamp('13-NOV-14 03.38.27.785000000 PM','DD-MON-RR HH.MI.SSXFF AM','NLS_DATE_LANGUAGE = AMERICAN')
See this for another alternative of altering session.