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

1
votes

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)

3
sqlstringoracle
Maybe this link will help: stackoverflow.com/questions/22542882/… – Jordan Rhode
Use to_date() with a format mask, or an ANSI date literal: date '2001-04-15'. Details are in the manual: docs.oracle.com/cd/E11882_01/server.112/e41084/… – a_horse_with_no_name
to_date('2001-04-15','yyyy-mm-dd') – Paul Maxwell
thank you for your help. @a_horse_with_no_name – E_T_A

3 Answers

2
votes

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')...

0
votes

OldProgrammers answer is the correct answer. Explicitly converting the string to a date is the safest. MS-SQL will generally automatically convert anything recognizable as a date and Oracle will do it if your formatting matches the default formatting for the system. All the oracle systems I have worked with used 'DD/MON/YY' or the two digit day, the three letter month abbreviation and the two digit year as their default and would automatically convert that. Not the proper way to do it but everybody likes to be lazy sometimes.

0
votes
ORA-01861: literal does not match format string

This happens because you have tried to enter a literal with a format string, but the length of the format string was not the same length as the literal.

You can overcome this issue by carrying out following alteration.

TO_DATE('2001-04-15','YYYY-MM-DD')

As a general rule, if you are using the TO_DATE function, TO_TIMESTAMP function, TO_CHAR function, and similar functions, make sure that the literal that you provide matches the format string that you've specified