Search code examples
sqloraclesql-timestamp

Timestamp conversion in Oracle SQL

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

Solution

  • 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.