ORA-01861: literal does not match format string

ORA-01861: literal does not match format string

A 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

  1. A DATE column is compared with a plain string
  2. An ISO 8601 timestamp with a T between date and time
  3. The date contains words like 년 or 年 between the numbers

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
Setuprun as lab_a
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;
OUTPUT
Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
SELECT order_id FROM orders WHERE order_date >= '2026-11-01';
OUTPUT
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/
Checkrun as lab_a
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
SELECT value FROM nls_session_parameters WHERE parameter = 'NLS_DATE_FORMAT';
OUTPUT
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.

After the fixrun as lab_a
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;
OUTPUT
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
Setuprun as lab_a
CREATE TABLE api_logs (log_id NUMBER PRIMARY KEY, created_at DATE);
OUTPUT
Table created.
Reproducerun as lab_a
INSERT INTO api_logs VALUES (1, TO_DATE('2026-11-12T14:30:00', 'YYYY-MM-DD HH24:MI:SS'));
OUTPUT
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/
Checkrun as lab_a
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;
OUTPUT
 SPACE_FMT      T_FMT
---------- ----------
         0          1

1 row selected.
After the fixrun as lab_a
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;
OUTPUT
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
Setuprun as lab_a
CREATE TABLE holidays (holiday_id NUMBER PRIMARY KEY, holiday_date DATE);
OUTPUT
Table created.
Reproducerun as lab_a
INSERT INTO holidays VALUES (1, TO_DATE('2026년 12월 25일', 'YYYY-MM-DD'));
OUTPUT
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/
Checkrun as lab_a
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;
OUTPUT
 PLAIN_FMT QUOTED_FMT
---------- ----------
         0          1

1 row selected.
After the fixrun as lab_a
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;
OUTPUT
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

Related errors