How to Fix ORA-01861: Literal Does Not Match Format String in Oracle

ORA-01861 means a string being converted to a date does not follow the format model: its parts come in a different order, it contains text the format does not mention, or, with exact matching, its separators differ. It also appears without any TO_DATE, when a string is compared with a date and Oracle converts it with the session's default format.

Reproduce the Error

Example:

select to_date('2026-03-15T10:00', 'YYYY-MM-DD HH24:MI') from dual;   -- the T is not in the format
select to_date('2026-03-15', 'DD-MON-YYYY') from dual;                -- value and format in different order
select to_date('2026/03/15', 'FXYYYY-MM-DD') from dual;                -- FX: separators must match exactly
select count(*) from dual where sysdate > '2026-03-15';               -- implicit conversion with NLS format

Output:

Error starting at line : 1
In command -
select to_date('2026-03-15T10:00', 'YYYY-MM-DD HH24:MI') from dual
Error at Command Line : 1 Column : 16
Error report -
SQL Error: ORA-01861: literal does not match format string

Error starting at line : 2
In command -
select to_date('2026-03-15', 'DD-MON-YYYY') from dual
Error at Command Line : 2 Column : 16
Error report -
SQL Error: ORA-01861: literal does not match format string

Error starting at line : 3
In command -
select to_date('2026/03/15', 'FXYYYY-MM-DD') from dual
Error at Command Line : 3 Column : 16
Error report -
SQL Error: ORA-01861: literal does not match format string

Error starting at line : 4
In command -
select count(*) from dual where sysdate > '2026-03-15'
Error at Command Line : 4 Column : 43
Error report -
SQL Error: ORA-01861: literal does not match format string

• The ISO timestamp has a T between date and time that the format does not describe.
• The string is year-first, the format day-first with a month name.
• The FX modifier demands exact matching, and the string uses slashes instead of hyphens.
• Comparing SYSDATE with the string converts it with NLS_DATE_FORMAT, here DD-MON-YYYY, which does not fit.

Fix: Match the Format, or Use a Literal

Example:

select to_date('2026-03-15T10:00', 'YYYY-MM-DD"T"HH24:MI') as iso_with_t from dual;
select to_date('2026-03-15', 'YYYY-MM-DD') as parsed from dual;
-- a date literal needs no format model at all
select count(*) as after_15_mar from dual where sysdate > date '2026-03-15';

Output:

ISO_WITH_T
______________
15-MAR-2026

PARSED
______________
15-MAR-2026

   AFTER_15_MAR
_______________
              1

Text in double quotes inside the format, "T", matches literally. A DATE literal is always year-month-day and does not depend on any setting.

Things to Know

• Without FX, Oracle accepts other punctuation than the format shows, such as slashes for hyphens; with FX it does not.
• Never compare dates with strings; convert explicitly or use DATE and TIMESTAMP literals.

Related Guides

• How to Fix ORA-01858: A Non-Numeric Character Was Found Where a Numeric Was Expected in Oracle
• Oracle Datetime Format Models Explained

Conclusion

ORA-01861 means a date string and its format model do not line up. Describe extra text with quoted literals, put parts in the right order, and replace implicit comparisons with explicit conversions or DATE literals.