Inserting custom TIMESTAMP value in the Oracle SQL

Question

I need to insert custom date time in the oracle database where the ITEM_TIME_DATE column has the TIMESTAMP data type.

Here is my query:

INSERT INTO items 
    (ITEM_AUTHOR_ID, ITEM_TITLE, ITEM_DESCRIPTION, ITEM_TIME_DATE, ITEM_STATUS) 
    VALUES 
    ('0', 'test title', 'test des', TO_TIMESTAMP('12-23-2015 8:00 PM', 'MM-DD-YYYY HH24:MI:SS'), 0);

Though the custom date should convert to a timestamp format, but i am getting this error:

ORA-01858: a non-numeric character was found where a numeric was expected

I am using Oracle 11.2.0.2.0 (Windows 10) 64-bit

Bob Jarvis - Слава Україні · Accepted Answer · 2015-12-29 14:38:57Z

2

The date format in the TO_TIMESTAMP call doesn't match the data. Use:

INSERT INTO items 
  (ITEM_AUTHOR_ID, ITEM_TITLE, ITEM_DESCRIPTION, ITEM_TIME_DATE, ITEM_STATUS) 
VALUES 
  ('0', 'test title', 'test des', TO_TIMESTAMP('12-23-2015 8:00 PM', 'MM-DD-YYYY HH:MI AM'), 0);

Best of luck.

answered Dec 29, 2015 at 14:38

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

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

Comments

54l3d · Accepted Answer · 2015-12-29 14:46:59Z

1

You need to remove the :SS or add seconds :00 to your time, and add AM to the format.

This works:

INSERT INTO items 
    (ITEM_AUTHOR_ID, ITEM_TITLE, ITEM_DESCRIPTION, ITEM_TIME_DATE, ITEM_STATUS) 
    VALUES 
    ('0', 'test title', 'test des', TO_TIMESTAMP('12-23-2015 8:00:00 PM', 'MM-DD-YYYY HH:MI:SS AM'), 0);

answered Dec 29, 2015 at 14:46

54l3d

3,9734 gold badges35 silver badges59 bronze badges

Comments

Incognito · Accepted Answer · 2015-12-29 14:39:08Z

0

You have used HH24:MI:SS as the date format and so your insert value should be

TO_TIMESTAMP('12-23-2015 8:00', 'MM-DD-YYYY HH24:MI:SS')

answered Dec 29, 2015 at 14:39

Incognito

3,1043 gold badges30 silver badges42 bronze badges

Collectives™ on Stack Overflow

Inserting custom TIMESTAMP value in the Oracle SQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related