Oracle error sql

Question

I have to create a table and insert values into the tables. The tables is being created but it wont insert values. heres my code-

CREATE TABLE CUSTOMER(
CID         number(20) not null primary key,        
FNAME       varchar2(50) not null,                  
MIDDLE      varchar2(50) null,
LNAME       varchar2(50) not null,
STREET      varchar2(50) not null,
CITY        varchar2(50) not null,
STATE       char(2) not null,
ZIP         varchar2(5) not null,
PHONE       varchar2(20) not null,
BIRTH_YR    date);

INSERT INTO CUSTOMER VALUES(
'1','john','paul','connor','Broad st','newark','nj','07103','2018643110','1992');

I keep getting an error

[Error] Script lines: 12-14 ------------------------
ORA-01861: literal does not match format string
Script line 14, statement line 2, column 85 

Can somebody help me out? What am i doing wrong?

Answer 1

You must change the table definition to something like below to allow insertion of year in the format you need:

CREATE TABLE CUSTOMER(
CID         number(20) not null primary key,        
FNAME       varchar2(50) not null,                  
MIDDLE      varchar2(50) null,
LNAME       varchar2(50) not null,
STREET      varchar2(50) not null,
CITY        varchar2(50) not null,
STATE       char(2) not null,
ZIP         varchar2(5) not null,
PHONE       varchar2(20) not null,
BIRTH_YR    char(4) not null);

Otherwise, you have to provide literal date values in the default format accepted by your Oracle database. You can use this query to find out what is the date format accepted by your system:

SELECT *
FROM nls_database_parameters
WHERE parameter LIKE '%DATE%'

For me, it returns:

NLS_DATE_FORMAT      |   DD-MON-RR
---------------------------------
NLS_DATE_LANGUAGE    |   AMERICAN

which means instead of '1992', I should insert values such as '01-Jan-1992' in the BIRTH_YR column:

INSERT INTO CUSTOMER
    VALUES('1','john','paul','connor','Broad st','newark','nj','07103','2018643110', '01-Jan-1992');

Answer 2

BIRTH_YR being a DATE field should be given in 'yyyy-MM-dd' format, '1992-01-01'.