Can't insert date into SQL table

Question

I've created the following table:

CREATE TABLE match(
match_id NUMBER(4,0), 
match_date DATE, 
attendance NUMBER(6,0), 
stadium_name VARCHAR2(40), 
tournament_id NUMBER(3,0),
CONSTRAINT match_id_pk PRIMARY KEY(match_id),
CONSTRAINT match_stadium_name_fk FOREIGN KEY(stadium_name) 
           REFERENCES stadium(stadium_name));

I attempt to insert the following line:

INSERT INTO match VALUES(1001, '20130515', 90000, 'American Airlines Arena', 001);

Everything I've found tells me the format is YYYYMMDD. However I keep getting ORA-01861: literal does not match format string.

After using (DESCRIBE match) it says the length is only 7. I thought it was supposed to be 10.

Thanks in advance for any help.

Have you tried supplying the date as `'05/15/2013'` – Linger Oct 04 '13 at 12:59 — Linger, Oct 04 '13 at 12:59
The length of what is only 7? – Dan Bracuk Oct 04 '13 at 13:02 — Dan Bracuk, Oct 04 '13 at 13:02

score 7 · Accepted Answer · answered Oct 04 '13 at 12:59

7

Well, the format YYYYMMDD is probably not the database date format (you should look for NLS_DATE_FORMAT to see what's the db date format).

But there's a NLS_DATE_FORMAT at the system level and a (maybe other) NLS_DATE_FORMAT at the session level.

You can achieve what you want using TO_DATE and specifying the format :

INSERT INTO match VALUES(1001, TO_DATE('20130515', 'YYYYMMDD'), 90000, 'American Airlines Arena', 001);

answered Oct 04 '13 at 12:59

Raphaël Althaus

59,727
6
96
122

Great! thank you very much. My text only seems to mention about querying with the TO_DATE I didn't know you could use it in an insert. – William Roeser Oct 04 '13 at 13:17

Can't insert date into SQL table

1 Answers1