Timestamp conversion in Oracle SQL

Question

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

Answer 1

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.