Vol. I · No. 47 Saturday, 21 November 2026 Wah Cantt, Pakistan
Oracle with Hassan

Tools & Plugins · Free tool

Oracle date format tester — TO_CHAR, explained.

Type a format mask and see exactly what Oracle returns, with every element explained and the common mistakes flagged. It runs entirely in your browser — nothing you type is sent anywhere.

Checked against Oracle AI Database 26ai48,440 test cases, character for characterEnglish / AMERICA settings

Oracle returns

Element by element

ElementOutputMeaning

Ready to paste

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:

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

ElementMeaningExample
YYYY
YYY
YY
Y
RRRR
RR
Y,YYY
YEAR
SYYYY
IYYY
IY
CC

Month

ElementMeaningExample
MM
MON
MONTH
RM
Q

Week & day

ElementMeaningExample
WW
IW
W
DDD
DD
D
DY
DAY
J

Time

ElementMeaningExample
HH24
HH12
HH
MI
SS
SSSSS
FF3
FF6
AM
A.M.

Other

ElementMeaningExample
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