- Error code
- ORA-01861
- Product
- Oracle Database
- Versions
- Oracle Database 19c, 21c, 23ai and 26ai (same wording in every release)
- Category
- Data Types & Conversion
- Last reviewed
- 24 October 2026
On this page
What it means
ORA-01861 means a string being converted to a date or timestamp does not match the format model used for the conversion, either the one passed to TO_DATE or TO_TIMESTAMP or, for implicit conversions, the session's NLS_DATE_FORMAT.
Oracle reads the text against the format model element by element. The 19c reference says literals in the input must be the same length as the literals in the format string, apart from leading whitespace, and that with the FX modifier the match must be exact. The current reference puts it simply: the literal did not match the format string, so correct one or the other.
The most common case is not a TO_DATE call at all: a DATE column is compared with a string such as '2026-10-24', so Oracle converts the string with the session NLS_DATE_FORMAT (for example DD-MON-RR), and the two do not match. The message shows neither the text nor the format. If only the month is wrong, Oracle raises ORA-01843 instead; a string longer than the format raises ORA-01830.
Common causes
- A DATE column is compared with, or a string inserted into, a date column using a layout that differs from the session NLS_DATE_FORMAT.
- The text contains a literal the format does not have, such as the T in ISO 8601 timestamps (2026-10-24T14:30:00) or a time zone suffix.
- The format model is written for a different layout, for example YYYY-MM-DD for text that reads 24.10.2026 14:30.
- FX (format exact) is in the format model, so separators and padding must match exactly.
- A column that is already a DATE is passed through TO_DATE, which first converts it to text with NLS_DATE_FORMAT and then reads that text with your format.
Diagnose it
Read-only These queries only read the data dictionary (USER_, ALL_ and DBA_ views) and dynamic performance views (V$). They do not change data, privileges or settings, so they are safe to run first. DBA_ and V$ views need the matching privileges.
See which format implicit conversions use
SELECT parameter, value
FROM nls_session_parameters
WHERE parameter IN ('NLS_DATE_FORMAT', 'NLS_TIMESTAMP_FORMAT', 'NLS_DATE_LANGUAGE');The formats this session applies when a string meets a DATE or TIMESTAMP with no explicit format. Run it from the same tool or application that raises the error.
Is the column already a date?
SELECT column_name, data_type
FROM all_tab_columns
WHERE owner = 'SHOP'
AND table_name = 'ORDERS'
AND column_name = 'ORDER_DATE';DATE or TIMESTAMP means the column needs no TO_DATE; compare it with a date value. VARCHAR2 means the stored text needs an explicit format.
Find stored text that does not fit the format
SELECT import_id, created_text
FROM shop.order_imports
WHERE created_text IS NOT NULL
AND VALIDATE_CONVERSION(created_text AS DATE, 'YYYY-MM-DD HH24:MI:SS') = 0;Each value that would raise a conversion error with that format. A handful of rows points to bad data; most rows point to the wrong format model.
How to fix it
Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.
Compare dates with DATE or TIMESTAMP literals
Read-onlyANSI literals have a fixed format and never depend on NLS settings.
SELECT order_id
FROM shop.orders
WHERE order_date >= DATE '2026-10-24'
AND created_at < TIMESTAMP '2026-10-25 00:00:00';Write the format model to match the text, including literals
Read-onlyText in double quotes inside a format model is matched literally, which handles the T in ISO 8601 values.
SELECT TO_TIMESTAMP('2026-10-24T14:30:00', 'YYYY-MM-DD"T"HH24:MI:SS') AS created_at
FROM dual;Do not wrap DATE columns in TO_DATE
Read-onlyTo drop the time part use TRUNC; to display a date use TO_CHAR with a format. Neither needs a round trip through text.
SELECT order_id, TO_CHAR(order_date, 'YYYY-MM-DD') AS order_day
FROM shop.orders
WHERE TRUNC(order_date) = DATE '2026-10-24';Set the session format only as a stopgap
Changes configurationChanging NLS_DATE_FORMAT for the session makes implicit conversions match your text, but only in that session; other clients keep their own setting. Prefer explicit formats in the SQL.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';Examples
Comparing a DATE column with an ISO date string
Not reproduced here: no Oracle Database is installed for this site. The result shows the message as documented in Oracle's error reference, which has the same wording in 26ai, 21c and 19c. Whether this fails depends on the session NLS_DATE_FORMAT; DATE '2026-10-24' works in every session.
-- session NLS_DATE_FORMAT is DD-MON-RR
SELECT order_id
FROM orders
WHERE order_date = '2026-10-24';ORA-01861: literal does not match format string
An ISO 8601 timestamp with a T separator
Not reproduced here: no Oracle Database is installed for this site. The message format is taken from Oracle's error reference. The T in the text has no counterpart in the format model; writing "T" in the format, as in the fix above, matches it.
SELECT TO_TIMESTAMP('2026-10-24T14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS created_at
FROM dual;ORA-01861: literal does not match format string
How to prevent it
Treat every string that meets a date as a conversion that needs an explicit format: DATE and TIMESTAMP literals for constants, TO_DATE or TO_TIMESTAMP with a full format model for text, and date-typed bind variables from the application.
Store dates in DATE or TIMESTAMP columns, not text, so comparisons need no conversion. When text dates must be imported, check them with VALIDATE_CONVERSION and the exact format first. See SQL data types explained.
Common questions
How do I fix ORA-01861 literal does not match format string?
Find the string being converted and the format used for it. If you passed a format, make it match the text exactly (including literal characters such as T in double quotes). If the conversion is implicit, replace the string with a DATE literal or TO_DATE with an explicit format.
Why does my query work in one tool and fail in another?
Implicit conversions use the session NLS_DATE_FORMAT, and each client sets its own. A string that matches one tool's format fails in the other. Explicit formats or DATE literals remove the dependency.
Should I use TO_DATE on a DATE column?
No. TO_DATE expects text, so Oracle first turns the DATE into text with NLS_DATE_FORMAT and then parses it with your format, which can fail or lose the century. Use TRUNC to remove the time and TO_CHAR to format a date for display.
Learn more on CodeWithSQL
Official documentation
- ORA-01861 (Oracle Database Error Messages)
- Format Models (Oracle SQL Language Reference 19c)
- Literals, including datetime literals (Oracle SQL Language Reference 19c)
- NLS_DATE_FORMAT (Oracle Database Reference 19c)
Last technically reviewed 24 October 2026; examples and diagnostics run on Oracle Database Error Messages reference (26ai, 21c and 19c); not run locally. Messages and explanations follow Oracle's Database Error Messages reference; no Oracle Database was available, so the examples were not run locally.