// Oracle error reference

ORA-01861: literal does not match format string

A string given to TO_DATE or an implicit date conversion doesn't match the format model, for example a different separator or field order.

Error code
ORA-01861
Category
Data values and conversion
Error message
literal does not match format string

What it means

ORA-01861 means the characters in a date string don't line up with the format model, such as TO_DATE('2026-11-09', 'DD/MM/YYYY'). Implicit conversions use the session's NLS_DATE_FORMAT, which is why this often appears when a string is compared with a DATE column.

Common causes

  • A format model that doesn't match the string's layout.
  • Comparing a DATE column with a string and relying on the session's date format.
  • Mixed data in a text column that stores dates in several layouts.

How to fix it

  1. Use a format model that matches the string exactly.

    SELECT TO_DATE('2026-11-09 14:30', 'YYYY-MM-DD HH24:MI') FROM dual;
  2. Use date and timestamp literals for constants.

    SELECT * FROM orders WHERE created_at >= TIMESTAMP '2026-11-09 00:00:00';
  3. Find rows that don't parse, on 12.2 and later.

    SELECT raw_date FROM staging WHERE VALIDATE_CONVERSION(raw_date AS DATE, 'YYYY-MM-DD') = 0;

Related errors

More data values and conversion errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.