How Oracle format masks work
A format mask (Oracle calls it a format model) tells TO_CHAR how to turn a DATE or TIMESTAMP into text, and tells TO_DATE how to read text back into a date. Oracle reads the mask from left to right and always takes the longest element it recognises: DDD is the day of the year, not DD followed by D.
Anything that is not a letter — dashes, slashes, colons, spaces, commas, brackets — is copied to the output as it is. Letters that are not part of an element raise ORA-01821: date format not recognized, so wrap literal words in double quotes: 'DD "of" Month'. Digits must be quoted too.
FM: why your month has extra spaces
MONTH and DAY are padded with blanks to the length of the longest name — nine characters in English, the length of SEPTEMBER and WEDNESDAY. Numbers are padded with leading zeros: DD gives 05, not 5.
FM (fill mode) removes both. It is a switch, not a setting: each FM in the mask turns fill mode on or off for everything after it. That is why 'FMDay, DD Month' and 'FMDay, FMDD Month' give different results — in the second, the second FM turns padding back on for the day number and the month.
TO_CHAR(DATE '2026-03-05', 'Day DD Month') → 'Thursday 05 March '
TO_CHAR(DATE '2026-03-05', 'FMDay DD Month') → 'Thursday 5 March'
MM or MI? The bug that hides for months
MM is the month and MI is the minutes. A mask like 'HH24:MM' is valid, so Oracle never complains — it simply prints the month where you expected minutes. At 09:45 on 5 March, 'HH24:MM' returns 09:03. The tester flags this pattern whenever MM appears after an hour element.
YYYY, YY or RR?
When formatting, YY and RR both print the last two digits of the year. The difference appears when reading two-digit years with TO_DATE:
YYkeeps the current century: in 2026,'99'becomes 2099.RRpicks the nearest century: in 2026,'00'–'49'become 2000–2049 and'50'–'99'become 1950–1999.YYYYavoids the question entirely. Use it for anything you store or exchange.
Upper, lower or initial capitals
Text elements copy the capitalisation you type: MONTH gives MARCH, Month gives March and month gives march. Oracle looks at the first two letters of the element, so MOnth still counts as upper case. The same rule decides the case of ordinal suffixes: DDth returns 05TH because DD is upper case, while Ddth returns 05th.
Language and territory settings
Month and day names follow NLS_DATE_LANGUAGE; the day number D, the territory formats DS, DL and TS, and the first day of the week follow NLS_TERRITORY. In AMERICA, Sunday is day 1; in most of Europe, Monday is. This tester uses English and AMERICA. For code that must behave the same everywhere, pass the language explicitly and use ISO weeks:
TO_CHAR(order_date, 'FMDay DD Month YYYY', 'NLS_DATE_LANGUAGE=ENGLISH')
TO_CHAR(order_date, 'IYYY"-W"IW') -- ISO week, the same in every territory
Format element reference
The elements you will use most often. Click any element in the tester above to see its exact output for your date.
Year
| Element | Meaning | Example |
|---|---|---|
YYYY | ||
YYY | ||
YY | ||
Y | ||
RRRR | ||
RR | ||
Y,YYY | ||
YEAR | ||
SYYYY | ||
IYYY | ||
IY | ||
CC |
Month
| Element | Meaning | Example |
|---|---|---|
MM | ||
MON | ||
MONTH | ||
RM | ||
Q |
Week & day
| Element | Meaning | Example |
|---|---|---|
WW | ||
IW | ||
W | ||
DDD | ||
DD | ||
D | ||
DY | ||
DAY | ||
J |
Time
| Element | Meaning | Example |
|---|---|---|
HH24 | ||
HH12 | ||
HH | ||
MI | ||
SS | ||
SSSSS | ||
FF3 | ||
FF6 | ||
AM | ||
A.M. |
Other
| Element | Meaning | Example |
|---|---|---|
AD | ||
DS | ||
DL | ||
TS | ||
FM | ||
FX |
Questions developers ask
Why does TO_CHAR(SYSDATE, 'DAY') = 'MONDAY' never match?
Because DAY is padded to nine characters: the value is 'MONDAY '. Compare with 'FMDAY', or better, avoid language-dependent names in logic and use TO_CHAR(d, 'D') with care, or ISO day arithmetic such as TRUNC(d) - TRUNC(d, 'IW') (0 = Monday).
How do I show milliseconds?
Only TIMESTAMP values have fractional seconds. Use FF3 for milliseconds or FF6 for microseconds: TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF3'). On a DATE, FF raises ORA-01821.
What is the ISO 8601 format in Oracle?
'YYYY-MM-DD"T"HH24:MI:SS'. The T must be in double quotes because it is a letter. For a timestamp with time zone, add TZH:TZM.
Is this exactly what my database will return?
For English and AMERICA settings, yes: every element, case variant, FM combination and suffix in this tool was compared with Oracle AI Database 26ai, plus 24,000 randomly generated masks, with identical results. Other languages and territories change names and the week start, so test those in your own session.
Also on this site: DBMS_SCHEDULER Interval Builder · DBMS_SCHEDULER tutorial