ORA-01861: literal does not match format string
ORA-01861: literal does not match format stringA date string has characters in places where the date format does not expect them.
Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-12
ORA-01861 appears when Oracle converts a string to a date and the characters in the string do not line up with the format. The format is either the one you give to TO_DATE, or the session's NLS_DATE_FORMAT when you give none.
The error marks the string with * but does not show the format. Compare the two side by side: every letter or word in the string that is not a date part (like T or 년) must also be in the format.
Causes and fixes
1A DATE column is compared with a plain string
WHERE order_date >= '2026-11-01' compares a DATE with a string, so Oracle converts the string with the session's NLS_DATE_FORMAT. Here it was DD-MON-RR, and 2026-11-01 does not fit that pattern.
With the date literal DATE '2026-11-01', or TO_DATE('2026-11-01', 'YYYY-MM-DD'), the same query returned orders 2 and 3 while the session was still DD-MON-RR.
Setup
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, order_date DATE);
INSERT INTO orders VALUES (1, DATE '2026-10-30');
INSERT INTO orders VALUES (2, DATE '2026-11-05');
INSERT INTO orders VALUES (3, DATE '2026-11-12');
COMMIT;Table created.
1 row created.
1 row created.
1 row created.
Commit complete.ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
SELECT order_id FROM orders WHERE order_date >= '2026-11-01';Session altered.
SELECT order_id FROM orders WHERE order_date >= '2026-11-01'
*
ERROR at line 1:
ORA-01861: literal does not match format string
Help: https://docs.oracle.com/error-help/db/ora-01861/ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';Session altered.
VALUE
----------------------------------------------------------------
DD-MON-RR
1 row selected.A query like this can work in one tool and fail in another, because each session can have a different NLS_DATE_FORMAT. Do not compare DATE columns with plain strings.
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
SELECT order_id FROM orders WHERE order_date >= DATE '2026-11-01' ORDER BY order_id;
SELECT order_id FROM orders WHERE order_date >= TO_DATE('2026-11-01', 'YYYY-MM-DD') ORDER BY order_id;Session altered.
ORDER_ID
----------
2
3
2 rows selected.
ORDER_ID
----------
2
3
2 rows selected.2An ISO 8601 timestamp with a T between date and time
APIs and JSON often send dates like 2026-11-12T14:30:00. The format YYYY-MM-DD HH24:MI:SS has a space where the string has T, so the conversion fails.
Put the T in the format in double quotes: YYYY-MM-DD"T"HH24:MI:SS. VALIDATE_CONVERSION returned 0 for the space format and 1 for the quoted one, and the row was stored as 2026-11-12 14:30:00.
Setup
CREATE TABLE api_logs (log_id NUMBER PRIMARY KEY, created_at DATE);Table created.INSERT INTO api_logs VALUES (1, TO_DATE('2026-11-12T14:30:00', 'YYYY-MM-DD HH24:MI:SS'));INSERT INTO api_logs VALUES (1, TO_DATE('2026-11-12T14:30:00', 'YYYY-MM-DD HH24:MI:SS'))
*
ERROR at line 1:
ORA-01861: literal does not match format string
Help: https://docs.oracle.com/error-help/db/ora-01861/SELECT VALIDATE_CONVERSION('2026-11-12T14:30:00' AS DATE, 'YYYY-MM-DD HH24:MI:SS') AS space_fmt,
VALIDATE_CONVERSION('2026-11-12T14:30:00' AS DATE, 'YYYY-MM-DD"T"HH24:MI:SS') AS t_fmt
FROM dual; SPACE_FMT T_FMT
---------- ----------
0 1
1 row selected.INSERT INTO api_logs VALUES (1, TO_DATE('2026-11-12T14:30:00', 'YYYY-MM-DD"T"HH24:MI:SS'));
SELECT log_id, TO_CHAR(created_at, 'YYYY-MM-DD HH24:MI:SS') AS created_at FROM api_logs;1 row created.
LOG_ID CREATED_AT
---------- -------------------
1 2026-11-12 14:30:00
1 row selected.3The date contains words like 년 or 年 between the numbers
Dates typed in Korean or Japanese, like 2026년 12월 25일 or 2027年1月1日, contain characters that YYYY-MM-DD does not have.
As with T, write those characters in the format in double quotes: YYYY"년" MM"월" DD"일" and YYYY"年"MM"月"DD"日". Both rows were stored, as 2026-12-25 and 2027-01-01.
Setup
CREATE TABLE holidays (holiday_id NUMBER PRIMARY KEY, holiday_date DATE);Table created.INSERT INTO holidays VALUES (1, TO_DATE('2026년 12월 25일', 'YYYY-MM-DD'));INSERT INTO holidays VALUES (1, TO_DATE('2026년 12월 25일', 'YYYY-MM-DD'))
*
ERROR at line 1:
ORA-01861: literal does not match format string
Help: https://docs.oracle.com/error-help/db/ora-01861/SELECT VALIDATE_CONVERSION('2026년 12월 25일' AS DATE, 'YYYY-MM-DD') AS plain_fmt,
VALIDATE_CONVERSION('2026년 12월 25일' AS DATE, 'YYYY"년" MM"월" DD"일"') AS quoted_fmt
FROM dual; PLAIN_FMT QUOTED_FMT
---------- ----------
0 1
1 row selected.INSERT INTO holidays VALUES (1, TO_DATE('2026년 12월 25일', 'YYYY"년" MM"월" DD"일"'));
INSERT INTO holidays VALUES (2, TO_DATE('2027年1月1日', 'YYYY"年"MM"月"DD"日"'));
SELECT holiday_id, TO_CHAR(holiday_date, 'YYYY-MM-DD') AS holiday_date FROM holidays ORDER BY holiday_id;1 row created.
1 row created.
HOLIDAY_ID HOLIDAY_DA
---------- ----------
1 2026-12-25
2 2027-01-01
2 rows selected.FAQ
Why does WHERE date_col = '2026-11-01' give ORA-01861?
The string is converted with the session's NLS_DATE_FORMAT. If that is DD-MON-RR, a YYYY-MM-DD string does not match. Use DATE '2026-11-01' or TO_DATE with an explicit format.
How do I convert an ISO 8601 string with T in Oracle?
Quote the T in the format: TO_DATE('2026-11-12T14:30:00', 'YYYY-MM-DD"T"HH24:MI:SS').
How can I test a date format without getting the error?
VALIDATE_CONVERSION('value' AS DATE, 'format') returns 1 if it converts and 0 if it does not, so you can try several formats in one query.
What is the difference between ORA-01861 and ORA-01843?
ORA-01861 means the characters of the string do not match the format at all. ORA-01843 means the layout matched but the value in the month position is not a valid month.
How we verify
Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.
lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free