3

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

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Omri
  • 1,436
  • 7
  • 31
  • 61
  • 1
    [I built an SQLFiddle for your problem](http://sqlfiddle.com/#!4/4bfcf), ran the INSERT statement in your question, and found it worked perfectly well. Thus, it appears to be a problem local to your system. Given the error I'm guessing that your database may be using a locale where the month names are not the English month names, but as I say that's just a guess. Best of luck. – Bob Jarvis - Слава Україні Feb 22 '15 at 15:26

1 Answers1

4

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.

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Thanks, it's working. Can you help me with the second question? I want to change the format of the dates. I could insert the data, but its in a format that i don't want to work with. – Omri Feb 22 '15 at 15:51
  • Oracle doesn't store dates in the format you see. You should be worried of a format only to display it. So, don't worry, simply use the insert statement that you have. Whenever you want to display the values using a SELECT query, then you use `TO_CHAR` along with desired **format model**. Please mark it as answered if it has helped you. – Lalit Kumar B Feb 22 '15 at 15:53
  • OK. Thanks. A few weeks ago i changed the display format with "alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'" , but it doesn't works now. Do you know why? – Omri Feb 22 '15 at 16:00
  • Alter session only sets the parameter settings for that session, once you disconnect/exit the session, there is no effect of alter session on the new sessions. You could use, `to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS','NLS_DATE_LANGUAGE='AMERICAN')` – Lalit Kumar B Feb 22 '15 at 18:51