Why Does My SQL INSERT Statement Result in ORA-01861: Literal Does Not Match Format String?

ORA-01861: Literal Does Not Match Format String

In SQL, the error "ORA-01861: literal does not match format string" occurs when inserting a data value into a table, and the format of the literal does not match the specified format string. This error often arises when attempting to insert a date value in a specific format.

Consider the following INSERT statement:

INSERT INTO Patient (PatientNo, PatientFirstName, PatientLastName, PatientStreetAddress, PatientTown, PatientCounty, PatientPostcode, DOB, Gender, PatientHomeTelephoneNumber, PatientMobileTelephoneNumber)
VALUES (121, 'Miles', 'Malone', '64 Zoo Lane', 'Clapham', 'United Kingdom', 'SW4 9LP', '1989-12-09', 'M', 02086950291, 07498635200);

In this statement, the DOB column is defined as a date. However, the literal '1989-12-09' is not in a recognized format for SQL dates. To resolve this issue, use the TO_DATE() function to convert the string literal into the correct format:

INSERT INTO Patient (PatientNo, PatientFirstName, PatientLastName, PatientStreetAddress, PatientTown, PatientCounty, PatientPostcode, DOB, Gender, PatientHomeTelephoneNumber, PatientMobileTelephoneNumber)
VALUES (121, 'Miles', 'Malone', '64 Zoo Lane', 'Clapham', 'United Kingdom', 'SW4 9LP', TO_DATE('1989-12-09', 'YYYY-MM-DD'), 'M', 02086950291, 07498635200);

By specifying the format string 'YYYY-MM-DD', SQL recognizes the literal '1989-12-09' as a date in the format year-month-day and successfully inserts it into the DOB column.

The above is the detailed content of Why Does My SQL INSERT Statement Result in ORA-01861: Literal Does Not Match Format String?. For more information, please follow other related articles on the PHP Chinese website!