Inserting timestamp in oracle timestamp column

Question

I am trying to insert the timestamp(6) data type value into the table by using following code:

INSERT INTO TOY_STORE (TOY_STORE_ID,TOY_STORE_NAME,CITY,PHONENUMBER,STORE_OPENING_TIME,STORE_CLOSING_TIME)
    VALUES(1,'Kid''s Cave','Delhi',9912312312,'2014-04-01 09:10:12','2014-04-01 21:42:05');

But it is giving be error SQL Error: ORA-01843: not a valid month 01843. 00000 - "not a valid month" *Cause:
*Action: Can someone rectify my code

Gordon Linoff · Accepted Answer · 2015-08-28 12:06:16Z

2

Oracle has a non-standard format for date. You can use that format (DD/MMM/YYYY), use the to_date() function, or the DATE/TIMESTAMP operator. I think this will work:

INSERT INTO TOY_STORE (TOY_STORE_ID,T OY_STORE_NAME, CITY,PHONENUMBER, 
                       STORE_OPENING_TIME, STORE_CLOSING_TIME)
    VALUES(1, 'Kid''s Cave',' Delhi', 9912312312,
           TIMESTAMP '2014-04-01 09:10:12', TIMESTAMP '2014-04-01 21:42:05');

answered Aug 28, 2015 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

user330315 Over a year ago

The "DATE/TIMESTAMP operator" is actually standard ANSI SQL to write a date/timestamp literal (and my preferred way to do so). Oracle's date format is not "non-standard", because every other way of writing a date/timestamp literal is non-standard as well and subject to implicit data type conversion (which depends on the locale settings of the client)

Gordon Linoff Over a year ago

@a_horse_with_no_name . . . Although you are correct, I think ISO standard formats should be accepted anywhere -- and most databases to recognize them.

Wernfried Domscheit · Accepted Answer · 2015-08-28 12:40:38Z

0

You can also execute this command before you do your insert:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'yyyy-mm-dd hh24:mi:ss';

answered Aug 28, 2015 at 12:40

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Inserting timestamp in oracle timestamp column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related