TO_CHAR converts the date of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format. This post explains Oracle To_Char date format examples with syntax and date format specifiers.
TO_CHAR(date) Syntax
TO_CHAR(date, format, [nls_param])
Date Format Specifiers
| Format Model | Description |
|---|---|
| CC, SCC | Century (S prefixes BC dates with a minus sign) |
| YYYY, SYYYY | Year (S prefixes BC dates with a minus sign) |
| IYYY | Year based on ISO standard |
| YYY, YY, Y | Last three, two or one digits of the year |
| IYY, IY, I | Last three, two or one digits of the ISO year |
| Y,YYY | (Four Y's with comma) put a comma in the year (1,995) |
| YEAR, SYEAR | Year spelled out (S prefixes BC dates with a minus sign) |
| RR | Last two digits of year in another century (allows for year 2000) |
| BC, AD | BC or AD indicator |
| B.C., A.D. | BC or AD indicators with periods |
| Q | Numeric quarter of the year (1-4 with Jan-Mar=1) |
| MM | 2 number month (Jan = 01) |
| RM | Roman numeral month |
| MONTH | Name of month spelled out (upper case - month is upper case) |
| MON | abbreviated name of month (upper case - month is upper case) |
| WW | Week of the year (1-53) |
| IW | Week of the year (1-52, 1-53) based on ISO standard |
| W | Week of month (1-5) |
| DDD | day of year (1-366) (Don't forget leap years) |
| DD | day of month (1-31) |
| D | day of week (1-7) |
| DAY | Name of day (upper case, day is upper case) |
| DY | Abbreviated name of day |
| J | Julian day (Number of days since January 1, 4712 BC) |
| AM,PM | Meridian indicator |
| A.M., P.M. | Meridian indicator with periods. |
| HH, HH12 | Hour of day (0-12) |
| HH24 | Use 24 hour clock for hours (1-24) |
| MI | Minute (0-59) |
| SS | Second (0-60) |
| SSSSS | (five S's) seconds past midnight. (0-86399) |
| None | Date must be in the format 'DD-MON-YY'; |
Oracle TO_CHAR Date Format Examples
1. Change date to DD/MM/YYYY format.
SELECT TO_CHAR (SYSDATE, 'DD/MM/YYYY') FROM DUAL;
Output
20/07/2018
2. Change date to DD/Month/YYYY with 24hr time format.
SELECT TO_CHAR (SYSDATE, 'DD/FMMonth/YYYY HH24:MI:SS') FROM DUAL;
Note: Above using FMMonth to suppress blank. If you will use the only Month then the output will be as below:
20/July /2018
Output
20/July/2018 11:36:30
3. Change date to just YYYYMM format.
SELECT TO_CHAR (SYSDATE, 'YYYYMM') FROM DUAL;
Output
201807
4. Get the day of the year, the month, and the week for the date.
SELECT TO_CHAR (SYSDATE, 'fmDDD DD D ') FROM DUAL;
Output
201 20 6
5. Get some detailed formatting for reporting purposes.
SELECT TO_CHAR (SYSDATE, '"In month "RM" of year "YEAR') FROM DUAL;
Output
In month VII of year TWENTY EIGHTEEN