ORA-01861: literal does not match format string

A date string does not fit the format mask, or the session’s default date format if you gave none. Compare the string with the mask character by character, or use DATE '2026-10-15'.

What ORA-01861 means

Oracle reads the text and the mask side by side. If the separators or the order do not match, it stops with ORA-01861. It often appears without any TO_DATE in sight: comparing a DATE column with a plain string makes Oracle use NLS_DATE_FORMAT, which may be DD-MON-RR.

Common causes of ORA-01861

A mask that does not fit the text, such as DD/MM/YYYY for 2026-10-15.
Comparing a DATE with a string: WHERE order_date > '2026-10-15'.
Mixed formats in a text column that stores dates.
A client or session with a different NLS_DATE_FORMAT.

How to fix ORA-01861

Make the mask match the text exactly.
Never compare a DATE with a bare string: use TO_DATE with a mask or a DATE literal.
See the session’s format: SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT'.
For mixed data, find bad rows with VALIDATE_CONVERSION (Oracle 12.2 and later).

ORA-01861 example

The mask must fit the text:

-- ORA-01861: literal does not match format string
SELECT TO_DATE('2026-10-15', 'DD/MM/YYYY') FROM dual;

-- Works
SELECT TO_DATE('2026-10-15', 'YYYY-MM-DD') FROM dual;

A bare string uses the session format:

-- ORA-01861 when NLS_DATE_FORMAT is DD-MON-RR
SELECT * FROM orders WHERE order_date > '2026-10-15';

-- Works everywhere
SELECT * FROM orders WHERE order_date > DATE '2026-10-15';

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-01861. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.