(opens in a new tab)

Oracle TO_DATE Format: Masks, NLS and ORA-01861

September 26, 2026 by Chat2DB Team

Converting between strings and dates is one of the most frequent tasks in Oracle SQL, and one of the most frequent sources of errors. TO_DATE turns text into a DATE, TO_CHAR turns a DATE back into text, and both depend on a format model (also called a format mask) that tells Oracle how the characters line up with years, months, days, and times.

When the mask and the data disagree, you get errors such as ORA-01861: literal does not match format string. When you leave the mask out, Oracle silently falls back to session settings like NLS_DATE_FORMAT, and code that worked in SQL Developer breaks in a batch job running with a different territory.

This guide covers the format elements you actually use, the FM and FX modifiers, NLS parameters, the RR versus YY century rules, timestamps and time zones, date literals, the common conversion errors with fixes, safe conversion with VALIDATE_CONVERSION and DEFAULT ... ON CONVERSION ERROR, and the index pitfalls of implicit conversion. All examples run on Oracle Database 19c and 23ai.

TO_DATE and TO_CHAR Basics

The signatures are symmetrical:

TO_DATE(char [, fmt [, 'nlsparam']])
TO_CHAR(date [, fmt [, 'nlsparam']])

A minimal round trip:

SELECT TO_DATE('2026-03-05 14:30:00', 'YYYY-MM-DD HH24:MI:SS') AS parsed
FROM dual;
 
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS formatted
FROM dual;

Two rules are worth stating up front:

1. An Oracle DATE always stores a time component (hours, minutes, seconds). If your mask has no time elements, TO_DATE sets the time to midnight.
2. A DATE has no display format of its own. What you see in a client is the result of an implicit TO_CHAR using NLS_DATE_FORMAT. If a query "loses" the time, the time is usually still there; the display mask just does not show it.

Oracle Date Format Elements

These are the elements you need for almost every conversion.

The case of name elements controls the case of the output: MON gives MAR, Mon gives Mar, and mon gives mar. The same applies to MONTH, DAY, and DY.

Punctuation (-, /, ,, ., ;, :) can be used directly in the mask. Any other literal text must be enclosed in double quotes:

SELECT TO_CHAR(SYSDATE, '"Week" IW", "IYYY') FROM dual;
 
SELECT TO_DATE('2026-03-05T14:30:00', 'YYYY-MM-DD"T"HH24:MI:SS') FROM dual;

The second example is the standard fix for ISO 8601 strings with a T separator. Without the quoted "T", Oracle raises ORA-01861.

FF is only valid for TIMESTAMP types. Using it in TO_DATE raises ORA-01821: date format not recognized, because DATE has no fractional seconds. Use TO_TIMESTAMP instead.

FM and FX Modifiers

FM: Fill Mode

By default, TO_CHAR pads month and day names to a fixed width and keeps leading zeros. That is why TO_CHAR(SYSDATE, 'Month DD, YYYY') returns March 05, 2026 with extra spaces. FM removes the padding and leading zeros:

SELECT TO_CHAR(DATE '2026-03-05', 'Month DD, YYYY')   AS padded,
       TO_CHAR(DATE '2026-03-05', 'FMMonth DD, YYYY') AS trimmed
FROM dual;
-- PADDED:  March     05, 2026
-- TRIMMED: March 5, 2026

FM is a toggle. Each occurrence switches fill mode on or off for the elements that follow, so 'FMMonth FMDD' suppresses padding on the month but restores the zero on the day.

FX: Format Exact

On input, Oracle is lenient by default. It lets any punctuation stand in for other punctuation, lets you omit leading zeros, and applies documented substitution rules (for example, MM also accepts MON and MONTH values). All of these succeed:

SELECT TO_DATE('2026/3/5', 'YYYY-MM-DD')  FROM dual;
SELECT TO_DATE('05-MAR-2026', 'DD-MM-YYYY') FROM dual;

That leniency is convenient but can hide dirty data. FX requires the input to match the mask exactly, including separators and field widths:

SELECT TO_DATE('2026/3/5', 'FXYYYY-MM-DD') FROM dual;
-- ORA-01861: literal does not match format string

Use FX in validation and ETL code where you want malformed input rejected rather than guessed.

NLS_DATE_FORMAT and NLS_DATE_LANGUAGE

When a mask is missing, Oracle uses the session's NLS_DATE_FORMAT. Month and day names are read and written in NLS_DATE_LANGUAGE. Check the current values:

SELECT parameter, value
FROM   nls_session_parameters
WHERE  parameter IN ('NLS_DATE_FORMAT', 'NLS_DATE_LANGUAGE',
                     'NLS_TIMESTAMP_FORMAT', 'NLS_TERRITORY');

Change them for the current session:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

The session value is set by the client. JDBC, ODP.NET, SQL*Plus, and GUI tools each derive defaults from client locale or environment variables such as NLS_LANG and NLS_DATE_FORMAT. That is why the same query can behave differently in two tools. The robust approach is to never rely on the session default: always pass an explicit mask.

For month and day names, pass the language as the third argument. This makes the conversion independent of the session:

SELECT TO_DATE('05-MAR-2026', 'DD-MON-YYYY',
               'NLS_DATE_LANGUAGE = AMERICAN') AS d
FROM dual;
 
SELECT TO_CHAR(DATE '2026-03-05', 'FMDay, DD Month YYYY',
               'NLS_DATE_LANGUAGE = FRENCH') AS fr
FROM dual;
-- Jeudi, 5 Mars 2026

Note that D (day of week number) and WW-style week calculations depend on NLS_TERRITORY, not the language. For portable week numbers, use the ISO elements IW and IYYY.

RR vs YY: Two-Digit Year Logic

Two-digit years are ambiguous, and the two elements resolve them differently.

• YY always uses the current century. In 2026, TO_DATE('99', 'YY') returns a date in 2099.
• RR uses a 50-year window. If the current year's last two digits are 00-49, an input of 00-49 maps to the current century and 50-99 maps to the previous century. If the current year's last two digits are 50-99, an input of 00-49 maps to the next century and 50-99 to the current one.

In 2026 that means:

SELECT TO_CHAR(TO_DATE('49', 'RR'), 'YYYY') AS rr_49,  -- 2049
       TO_CHAR(TO_DATE('50', 'RR'), 'YYYY') AS rr_50,  -- 1950
       TO_CHAR(TO_DATE('99', 'YY'), 'YYYY') AS yy_99   -- 2099
FROM dual;

The classic bug is a birth date stored as '15-JUN-72' and parsed with YY, producing 2072. Use RR when you must accept two-digit years, and prefer four-digit years everywhere else. RRRR is a practical choice for input that mixes both: four digits are taken as-is, two digits follow the RR rule.

TO_TIMESTAMP and TO_TIMESTAMP_TZ

TIMESTAMP adds fractional seconds; TIMESTAMP WITH TIME ZONE adds an offset or region.

SELECT TO_TIMESTAMP('2026-03-05 14:30:00.123456',
                    'YYYY-MM-DD HH24:MI:SS.FF6') AS ts
FROM dual;
 
SELECT TO_TIMESTAMP_TZ('2026-03-05 14:30:00 +01:00',
                       'YYYY-MM-DD HH24:MI:SS TZH:TZM') AS ts_offset
FROM dual;
 
SELECT TO_TIMESTAMP_TZ('2026-03-05 14:30:00 Europe/Paris',
                       'YYYY-MM-DD HH24:MI:SS TZR') AS ts_region
FROM dual;

A full ISO 8601 string with fractional seconds and offset:

SELECT TO_TIMESTAMP_TZ('2026-03-05T14:30:00.250+01:00',
                       'YYYY-MM-DD"T"HH24:MI:SS.FF3TZH:TZM') AS iso
FROM dual;

To get a DATE out of a timestamp, use CAST(ts AS DATE), which drops the fractional seconds. To format a timestamp, TO_CHAR accepts the same elements, including FF and the time zone elements.

Date and Timestamp Literals

If the value is a constant in your SQL, you do not need TO_DATE at all. ANSI literals have a fixed format that does not depend on NLS settings:

SELECT DATE '2026-03-05' FROM dual;
 
SELECT TIMESTAMP '2026-03-05 14:30:00' FROM dual;
 
SELECT TIMESTAMP '2026-03-05 14:30:00.500 +01:00' FROM dual;

DATE literals always use YYYY-MM-DD and have no time part. TIMESTAMP literals use YYYY-MM-DD HH24:MI:SS with optional fractional seconds and time zone. Literals are the cleanest way to write range filters:

SELECT order_id, order_date
FROM   orders
WHERE  order_date >= DATE '2026-03-01'
AND    order_date <  DATE '2026-04-01';

Common Conversion Errors and Fixes

Error texts below are those printed by Oracle 19c. Oracle 23ai may add extra explanatory text, but the error numbers are the same.

ORA-01861: literal does not match format string

The input contains text that does not line up with the mask. Typical causes:

• A literal character in the data that is not in the mask, such as the T in ISO strings.
• An implicit conversion using NLS_DATE_FORMAT that does not match the string.
• FX mode rejecting a separator or field width difference.

-- Fails
SELECT TO_DATE('2026-03-05T14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
 
-- Works
SELECT TO_DATE('2026-03-05T14:30:00', 'YYYY-MM-DD"T"HH24:MI:SS') FROM dual;

The implicit version is harder to spot:

-- Depends on NLS_DATE_FORMAT; fails in many sessions
SELECT * FROM orders WHERE order_date = '2026-03-05';
 
-- Explicit and portable
SELECT * FROM orders WHERE order_date = DATE '2026-03-05';

Depending on the string and the session mask, the implicit form can raise ORA-01861, ORA-01843, or another conversion error. The fix is the same: never compare a DATE column with a bare string.

ORA-01843: not a valid month

The month field was parsed but its value is not a valid month. Two usual causes: day and month swapped, or month names in a language that does not match NLS_DATE_LANGUAGE.

-- Day and month swapped: 13 is not a month
SELECT TO_DATE('2026-13-05', 'YYYY-MM-DD') FROM dual;
 
-- Correct mask for day-first data
SELECT TO_DATE('13/05/2026', 'DD/MM/YYYY') FROM dual;
 
-- English month name parsed in a German session: fix with the NLS argument
SELECT TO_DATE('05-MAY-2026', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') FROM dual;

ORA-01830: date format picture ends before converting entire input string

The mask is shorter than the input. Most often the string has a time part and the mask does not:

-- Fails
SELECT TO_DATE('2026-03-05 14:30:00', 'YYYY-MM-DD') FROM dual;
 
-- Works
SELECT TO_DATE('2026-03-05 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM dual;
 
-- If you really want only the date part
SELECT TO_DATE(SUBSTR('2026-03-05 14:30:00', 1, 10), 'YYYY-MM-DD') FROM dual;

Timestamps exported with fractional seconds cause the same error when parsed with TO_DATE. Use TO_TIMESTAMP with FF, then CAST to DATE if needed.

ORA-01858: a non-numeric character was found where a numeric was expected

A numeric element such as DD, YYYY, or HH24 met a letter. This usually means the elements are in the wrong order, or the column contains non-date values like N/A.

-- Fails: DD receives 'Mar'
SELECT TO_DATE('Mar 05 2026', 'DD MON YYYY') FROM dual;
 
-- Works
SELECT TO_DATE('Mar 05 2026', 'MON DD YYYY',
               'NLS_DATE_LANGUAGE = AMERICAN') FROM dual;

When the problem is bad rows rather than a bad mask, find them with the techniques in the next section.

Safe Conversion: VALIDATE_CONVERSION and DEFAULT ON CONVERSION ERROR

Oracle Database 12.2 introduced two features that make conversion of dirty data much easier. Both are available in 19c and 23ai.

VALIDATE_CONVERSION returns 1 if the value can be converted and 0 if not. Use it to locate bad rows before a migration:

SELECT id, raw_date
FROM   staging_orders
WHERE  VALIDATE_CONVERSION(raw_date AS DATE, 'YYYY-MM-DD') = 0;

It also accepts the NLS argument:

SELECT VALIDATE_CONVERSION('05-MAR-2026' AS DATE, 'DD-MON-YYYY',
                           'NLS_DATE_LANGUAGE = AMERICAN') AS ok
FROM dual;

DEFAULT ... ON CONVERSION ERROR lets TO_DATE, TO_TIMESTAMP, and other conversion functions return a fallback instead of raising an error:

SELECT id,
       TO_DATE(raw_date DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD') AS order_date
FROM   staging_orders;

A practical load pattern is to insert the clean rows and keep the rejected ones for review:

INSERT INTO orders (id, order_date)
SELECT id, TO_DATE(raw_date, 'YYYY-MM-DD')
FROM   staging_orders
WHERE  VALIDATE_CONVERSION(raw_date AS DATE, 'YYYY-MM-DD') = 1;
 
SELECT id, raw_date
FROM   staging_orders
WHERE  VALIDATE_CONVERSION(raw_date AS DATE, 'YYYY-MM-DD') = 0;

Be careful with a non-null default. A value like DATE '1900-01-01' silently turns bad data into valid-looking data, which is usually worse than a NULL you can query for.

Implicit Conversion and Index Pitfalls

Implicit conversion does more than make code fragile; it can also stop Oracle from using indexes.

Date column compared with a string

WHERE order_date = '05-MAR-26' converts the string to a date, so the index on order_date can still be used. The problem here is correctness, not speed: the result depends on NLS_DATE_FORMAT. Use a literal or explicit TO_DATE.

String column compared with a date

The reverse case is a performance trap. If dates are stored in a VARCHAR2 column and compared with a DATE, Oracle converts the column, which means an implicit function applied to every row:

-- raw_date is VARCHAR2 with an index
SELECT * FROM staging_orders WHERE raw_date = DATE '2026-03-05';
-- Oracle effectively evaluates TO_DATE(raw_date) = DATE '2026-03-05'
-- so the index on raw_date is not usable, and bad rows raise errors

Compare strings with strings instead, or better, store dates as DATE:

SELECT * FROM staging_orders WHERE raw_date = '2026-03-05';

TRUNC on the indexed column

A very common pattern for "all orders on a day" is:

SELECT * FROM orders WHERE TRUNC(order_date) = DATE '2026-03-05';

This wraps the column in a function, so a normal index on order_date is not used for a range scan. Rewrite it as a half-open range:

SELECT *
FROM   orders
WHERE  order_date >= DATE '2026-03-05'
AND    order_date <  DATE '2026-03-05' + 1;

If the TRUNC form must stay, create a function-based index on TRUNC(order_date).

To confirm what Oracle did, check the predicate section of the execution plan for INTERNAL_FUNCTION or TO_DATE applied to your column:

EXPLAIN PLAN FOR
SELECT * FROM staging_orders WHERE raw_date = DATE '2026-03-05';
 
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(format => 'BASIC +PREDICATE'));

Step-by-Step Checklist

When a date conversion fails or returns the wrong value:

1. Read the error number. ORA-01861 means literal mismatch, ORA-01830 means the mask is too short, ORA-01843 means a bad month value, ORA-01858 means a letter where a number was expected.
2. Print the raw input with DUMP(col) or '[' || col || ']' to reveal hidden spaces, T separators, or fractional seconds.
3. Write an explicit mask that matches the input character by character, quoting any literal text.
4. Add the NLS argument if the data contains month or day names.
5. Use RR or four-digit years, never YY, for historical data.
6. Switch to TO_TIMESTAMP or TO_TIMESTAMP_TZ if the input has fractional seconds or a time zone.
7. Find bad rows with VALIDATE_CONVERSION and load the rest with DEFAULT NULL ON CONVERSION ERROR.
8. Replace string comparisons against date columns with DATE literals and half-open ranges.

If you do not want to build masks by hand, the free Oracle Date Format Builder (opens in a new tab) lets you pick elements and preview the TO_DATE and TO_CHAR output for a sample value.

Working with Oracle Dates in Chat2DB

Most date bugs are found by running a conversion against real rows and looking at the result. Chat2DB (opens in a new tab) connects to Oracle 19c and 23ai, lets you run VALIDATE_CONVERSION queries against staging tables, inspect NLS_SESSION_PARAMETERS, and view execution plans side by side, and its AI assistant can draft the correct format mask from a sample string when you describe the input in plain English.

Summary

Always pass an explicit format mask to TO_DATE and TO_CHAR, and add NLS_DATE_LANGUAGE when names are involved. Use FM to control output padding and FX to enforce strict input. Prefer four-digit years, or RR when two digits are unavoidable. Use ANSI DATE and TIMESTAMP literals for constants, TO_TIMESTAMP_TZ for time zones, and VALIDATE_CONVERSION with DEFAULT ... ON CONVERSION ERROR for dirty data. Finally, keep functions off indexed columns: compare dates with dates, and filter days with half-open ranges.