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]CC | Century; 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 |
| Q | Quarter (1,2,3,4) |
| MM | Month (01-12) |
| MONTH | Month 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) |
| HH24 | Hour 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 |
|---|---|
| FM | Fill mode toggle |
| TH | Ordinal number (e.g., 4th) |
| SP | Spelled-out number (e.g., four) |
| THSP, SPTH | Spelled-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> SQL>-- from w ww . j av a 2s. c om SQL> select to_char(sysdate,'DAY dy Dy') as day 2 , to_char(sysdate,'MONTH mon') as month 3 from dual; DAY -------------------------------------------------------------- MONTH ------------------------------------------------- SATURDAY sat Sat APRIL apr SQL>