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
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
-
Use a format model that matches the string exactly.
SELECT TO_DATE('2026-11-09 14:30', 'YYYY-MM-DD HH24:MI') FROM dual; -
Use date and timestamp literals for constants.
SELECT * FROM orders WHERE created_at >= TIMESTAMP '2026-11-09 00:00:00';
-
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
ORA-01843not a valid monthORA-01830date format picture ends before end of input241SQL Server: Conversion failed for date and/or time22007PostgreSQL: invalid datetime format
More data values and conversion errors
ORA-12899value too large for columnORA-01401inserted value too large for columnORA-01438value larger than specified precisionORA-01722invalid numberORA-00932inconsistent datatypesORA-01476divisor is equal to zero
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.