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

I built an SQLFiddle for your problem, 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 - Слава Україні
– Bob Jarvis - Слава Україні, Commented Feb 22, 2015 at 15:26

Community · Accepted Answer · 2017-05-23 11:56:56Z

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.

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Feb 22, 2015 at 15:42

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Omri Over a year ago

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.

Lalit Kumar B Over a year ago

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.

Omri Over a year ago

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?

Lalit Kumar B Over a year ago

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')

Collectives™ on Stack Overflow

Timestamp conversion in Oracle SQL

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related