SQL Error: ORA-01861: literal does not match format string

oraclesqlstring

I keep getting this error in Oracle when i try to run this statement. I am not sure where the formatting error is coming from. maybe someone with fresh eyes can assist me with this problem.

INSERT INTO Faculty
(FacNo, FacFirstName, FacLastName, FacCity, FacState,
 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
 VALUES ('543-21-0987','VICTORIA','EMMANUEL','BOTHELL','WA','MS','PROF',120000.0,'','2001-04-15','98011-2242');

Here is the error message i keep getting:

Error starting at line : 1 in command – Error report –
SQL Error: ORA-01861: literal does not match format string
01861. 00000 – "literal does not match format string"
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
"FX" modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.

Here are the specs on the table i am trying to INSERT this data into:

FACNO CHAR(11 BYTE)
FACFIRSTNAME VARCHAR2(30 BYTE)
FACLASTNAME VARCHAR2(30 BYTE)
FACCITY VARCHAR2(30 BYTE)
FACSTATE CHAR(2 BYTE)
FACZIPCODE CHAR(10 BYTE)
FACRANK CHAR(4 BYTE)
FACHIREDATE DATE
FACSALARY NUMBER(10,2)
FACSUPERVISOR CHAR(11 BYTE)
FACDEPT CHAR(6 BYTE)

Best Answer

Most likely, your NLS_DATE_FORMAT, the default date format for literals does not match your string. Never assume dates are formatted one way or another. use TO_DATE function to specify the format, so convert to :

Insert (... to_date('2001-04-15','YYYY-MM-DD')...

Related Topic