Having trouble inserting DATE into SQL table

Question

I keep getting a "ORA-01861: literal does not match format string ORA-06512: at "SYS.DBMS_SQL", line 1721" error when trying to insert a DATE value into the credit_card table listed below:

    CREATE TABLE CREDIT_CARD 
( 
  cc_card_number VARCHAR(16) NOT NULL, 
  cc_name_on_cc VARCHAR(100) NOT NULL, 
  cc_expiration_date DATE NOT NULL, 
  customer_id INT NOT NULL, 
  PRIMARY KEY (cc_card_number), 
  FOREIGN KEY (customer_id) REFERENCES CUSTOMER(customer_id) 
)

Here is the INSERT VALUES statement that keeps giving me an error:

INSERT INTO credit_card VALUES ('1111222233334444', 'Bob LoBlah', '1980-12-17', 1);

What am I doing wrong?

Thanks

Answer 1

Use the date keyword:

INSERT INTO credit_card
    VALUES ('1111222233334444', 'Bob LoBlah', DATE '1980-12-17', 1);

This allows you to use ISO standard date formats for date constants.

I also strongly encourage you to list the columns in the table being inserted:

INSERT INTO CREDIT_CARD (cc_card_number, cc_name_on_cc, cc_expiration_date , customer_id)
    VALUES ('1111222233334444', 'Bob LoBlah', DATE '1980-12-17', 1);

This prevents inadvertent errors.