Every time Oracle turns a date into text, or text into a date, a format model decides how the text looks: 'DD-MON-YYYY', 'YYYY-MM-DD HH24:MI', 'fmDay, Month DD'. TO_CHAR uses it to write dates, TO_DATE, TO_TIMESTAMP, and TO_TIMESTAMP_TZ use it to read them, and when you give none, the session's format is used instead. Knowing the elements and modifiers saves a lot of guesswork, and avoids a few classic bugs.
Code for This Guide
The main examples are in the examples/format-models folder of the Oracle Database 26ai code repository on GitHub, each with its output. The examples need no tables; the repository also has the NIMBUS sample schema in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
How a Format Model Is Built
A datetime format model is a sequence of elements, each producing or reading one part of the value. Punctuation (/ - , . ; 🙂 and text in double quotes are copied as they are. The case of an element sets the case of words: MONTH gives MARCH, Month gives March, and month gives march.
This example applies 29 elements to 15 March 2026, 16:05:09.123456 in India. The element list comes from a VALUES constructor, so each element becomes a row.
Example:
select element,
to_char(timestamp '2026-03-15 16:05:09.123456 Asia/Kolkata', element) as result
from (values ('YYYY'), ('YY'), ('RRRR'), ('YEAR'), ('Q'), ('MM'), ('MON'), ('Month'),
('WW'), ('IW'), ('W'), ('DDD'), ('DD'), ('D'), ('DY'), ('Day'), ('J'),
('HH'), ('HH24'), ('MI'), ('SS'), ('SSSSS'), ('FF3'), ('AM'),
('TZR'), ('TZH:TZM'), ('TZD'), ('DL'), ('TS')) t (element);Output:
ELEMENT RESULT __________ _________________________ YYYY 2026 YY 26 RRRR 2026 YEAR TWENTY TWENTY-SIX Q 1 MM 03 MON MAR Month March WW 11 IW 11 W 3 DDD 074 DD 15 D 1 DY SUN Day Sunday J 2461115 HH 04 HH24 16 MI 05 SS 09 SSSSS 57909 FF3 123 AM PM TZR ASIA/KOLKATA TZH:TZM +05:30 TZD IST DL Sunday, March 15, 2026 TS 4:05:09 PM 29 rows selected.
The Elements
| Element | Meaning |
|---|---|
| YYYY, YYY, YY, Y | Year: 4, 3, 2, or 1 last digits |
| RR, RRRR | Two-digit year in the nearest century |
| YEAR, SYYYY | Year spelled out; year with a minus sign for BC dates |
| IYYY, IY, IW | ISO year and ISO week (weeks start on Monday) |
| Q, MM, RM | Quarter; month number; month in Roman numerals |
| MON, MONTH | Abbreviated or full month name |
| WW, W | Week of the year from January 1; week of the month from the 1st |
| DDD, DD, D | Day of the year; day of the month; day of the week (by NLS_TERRITORY) |
| DY, DAY | Abbreviated or full weekday name |
| J | Julian day: days since 1 January 4712 BC |
| HH, HH12, HH24 | Hour 01 to 12; hour 00 to 23 |
| MI, SS, SSSSS | Minute; second; seconds past midnight |
| FF1 to FF9 | Fractional seconds with 1 to 9 digits |
| AM, PM | Meridian indicator |
| TZH, TZM, TZR, TZD | Time zone hour and minute offset, region, daylight saving abbreviation |
| DL, DS, TS | Long date, short date, and time in the locale's format |
A frequent mistake is MM where MI was meant: MM is the month, MI the minute. Another is HH without AM or PM, which shows 04 for both 4 in the morning and 4 in the afternoon; use HH24.
FM, TH, and SP
FM (fill mode) removes the blanks that pad names to a fixed width and the leading zeros of numbers. The suffix TH adds an ordinal ending, SP spells a number out, and SPTH spells it as an ordinal.
Example:
select to_char(date '2026-03-05', 'Month DD') as padded,
to_char(date '2026-03-05', 'fmMonth DD') as fill_mode,
to_char(date '2026-03-05', 'fmDdspth "of" Month') as spelled,
to_char(date '2026-03-05', 'DDth') as ordinal
from dual;
select to_date('5/3/2026', 'DD/MM/YYYY') as lenient from dual;Output:
PADDED FILL_MODE SPELLED ORDINAL _______________ ____________ _________________ __________ March 05 March 5 Fifth of March 05TH LENIENT ______________ 05-MAR-2026
Without FM, Month is padded to the length of the longest month name, September. The second query shows that TO_DATE is lenient by default: it accepts 5 for DD and 3 for MM.
FM Is a Toggle
FM switches fill mode on for every element after it, and a second FM switches it off again. Leaving it on can strip zeros you wanted to keep, such as the minutes:
Example:
-- FM stays on for every later element: MI loses its leading zero
select to_char(date '2026-03-15' + 0.5, 'DD "of" fmMonth YYYY, HH24:MI') as fm_left_on,
to_char(date '2026-03-15' + 0.5, 'DD "of" fmMonth fmYYYY, HH24:MI') as fm_switched_off
from dual;Output:
FM_LEFT_ON FM_SWITCHED_OFF _________________________ __________________________ 15 of March 2026, 12:0 15 of March 2026, 12:00
FX for Exact Input
FX (format exact) makes TO_DATE require the text to match the format exactly. With it, '5/3/2026' no longer matches DD/MM/YYYY, because DD and MM need two digits.
Example:
select to_date('5/3/2026', 'fxDD/MM/YYYY') as exact from dual;Output:
Error starting at line : 1
In command -
select to_date('5/3/2026', 'fxDD/MM/YYYY') as exact from dual
Error at Command Line : 1 Column : 16
Error report -
SQL Error: ORA-01862: the numeric value does not match the length of the format itemUse FX when you validate input that must follow one strict layout.
Two-Digit Years: YY and RR
YY puts a two-digit year in the current century, so 95 becomes 2095. RR puts it in the century that makes it nearest to the current year: with the current year in the first half of a century, 00 to 49 fall in this century and 50 to 99 in the previous one.
Example:
select to_char(to_date('15-MAR-95', 'DD-MON-YY'), 'YYYY') as yy_95,
to_char(to_date('15-MAR-95', 'DD-MON-RR'), 'YYYY') as rr_95,
to_char(to_date('15-MAR-49', 'DD-MON-RR'), 'YYYY') as rr_49,
to_char(to_date('15-MAR-50', 'DD-MON-RR'), 'YYYY') as rr_50
from dual;Output:
YY_95 RR_95 RR_49 RR_50 ________ ________ ________ ________ 2095 1995 2049 1950
Use RR when you must read two-digit years, and four-digit years everywhere else.
The Session Format
The session parameters NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT are format models too, used whenever a date is converted without one. Changing them changes the output of every query that relies on them:
Example:
-- the session format applies whenever a date is converted without a format model
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI';
select date '2026-03-15' + 0.5 as noon_default from dual;
alter session set nls_date_format = 'DD-MON-YYYY';
select date '2026-03-15' + 0.5 as noon_default,
to_char(date '2026-03-15' + 0.5, 'DD "of" Month YYYY, HH24:MI') as explicit
from dual;Output:
Session altered. NOON_DEFAULT ___________________ 2026-03-15 12:00 Session altered. NOON_DEFAULT EXPLICIT _______________ ______________________________ 15-MAR-2026 15 of March 2026, 12:00
With DD-MON-YYYY, the time of day disappears from the default display, though it is still stored. Code that converts dates should always pass an explicit format model, so it behaves the same in every session.
Language
NLS_DATE_LANGUAGE sets the language of month and day names, and NLS_TERRITORY the first day of the week used by D. Both come from the session, but TO_CHAR and TO_DATE accept the language as a last argument for one conversion, for example to_char(d, 'Day DD Month YYYY', 'NLS_DATE_LANGUAGE = German').
Related Guides
- Oracle TO_CHAR (Date) Function: A Simple Guide to Formatting Dates
- How to Use Pseudocolumns in Oracle
Conclusion
A datetime format model is a sequence of elements such as DD, MON, YYYY, HH24, MI, FF, and TZR, with punctuation and quoted text in between, and the case of an element sets the case of names. FM removes padding and is a toggle, FX demands exact input, TH and SP add ordinals and spelled numbers, RR reads two-digit years into the nearest century, and an explicit format model protects your code from session settings.
