Oracle SQL - to_char Conversion Function Formats


The following table showed that several Oracle conversion functions support an optional format (fmt) argument.

These format arguments allow you to deviate from the default conversion.

Format Description
[S]CCCentury; S stands for the minus sign (BC)
[S]YYYY Year, with or without minus sign
YYY, YY, Y Last 3, 2, or 1 digits of the year
[S]YEAR Year spelled out, with or without minus sign ( S)
BC, AD BC/AD indicator
QQuarter (1,2,3,4)
MM Month (01-12)
MONTHMonth name, padded with spaces to length 9
MON Month name, abbreviated (three characters)
WW, IW (ISO) week number (01-53)
W Week number within the month (1-5)
DDD Day number within the year (1-366)
DD Day number within the month (1-31)
D Day number within the week (1-7)
DAY Day name, padded with spaces to length 9
DY Day name abbreviation (three characters)
J Julian date; day number since 01/01/4712 BC
AM, PM AM/PM indicator
HH[12] Hour within the day (01-12)
HH24Hour within the day (00-23)
MI Minutes within the hour (00-59)
SS Seconds within the minute (00-59)
SSSSS Seconds after midnight (0-86399)
/., Punctuation characters; displayed verbatim (between date fields)
"..." String between double quotes displayed within the date expression

You can influence several date characteristics, such as the first day of the week, with the NLS_TERRITORY parameter.

Oracle can further refine the results of those functions with the following Format Component Additions.

Addition Description
FMFill mode toggle
THOrdinal number (e.g., 4th)
SPSpelled-out number (e.g., four)
THSP, SPTHSpelled-ordinal number (e.g., fourth)

In fill mode , Oracle does not perform padding with spaces, and numbers are not prefixed with leading zeros.

You can enable and disable this fill mode mechanism within the same format string as many times as you like, by repeating FM. It is a toggle.

Ordinal numbers indicate a relative position in a sequence.

The conversion function formats are case-sensitive, as demonstrated in the following code.


SQL>--  ww  w  .j ava2s.  c  om
SQL> select to_char(sysdate,'DAY dy Dy') as day
  2  ,      to_char(sysdate,'MONTH mon') as month
  3  from dual;

APRIL     apr


Related Topic