How to Resolve the ORA-01861: 'Literal Does Not Match Format String' Error in SQL?

ORA-01861: Resolving "Literal Does Not Match Format String" Error in SQL

When attempting to insert data into a database, users may encounter an ORA-01861 error stating that "literal does not match format string." This error occurs when the format of a literal used in an SQL statement does not align with the expected format defined in the format string.

In the provided example, the error is raised due to the use of a string literal for the DOB column. The error message indicates that the literal '1989-12-09' does not adhere to the expected date format. To resolve this issue, the string literal should be replaced with an expression that converts the date value to a recognized date format.

The recommended solution is to use the TO_DATE() function to convert the string literal to a suitable date format. In this case, the expression would be:

TO_DATE('1989-12-09','YYYY-MM-DD')

By utilizing this expression, the date value will be converted to the 'YYYY-MM-DD' format, which is compatible with the format expected by the DOB column. This corrected statement should execute successfully without encountering the ORA-01861 error:

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 adhering to the correct format specifications, developers can prevent the ORA-01861 error and ensure the successful execution of SQL statements.

The above is the detailed content of How to Resolve the ORA-01861: 'Literal Does Not Match Format String' Error in SQL?. For more information, please follow other related articles on the PHP Chinese website!