Oracle To_char() is an SQL function which will convert a number or date to string.
ORACLE TO_CHAR Function Syntax
The Oracle to_date() function’s syntax could be one of the following:
to_char(value)
to_char(value, format)
to_char(value, format, nls_language)
value: number or date, which will be converted to string.
format: the format we will use to convert a number or date to string.
The format mask parameters for date expression are listed as following: (They could be used in combinations)
| Parameter | Explanation |
|---|---|
| YEAR | Year in characters |
| YYYY | 4-digit year |
| YY | 2-digit year |
| IYYY | 4-digit ISO year |
| IY | 2-digit ISO year |
| RRRR | Take a 2-digit year as input and a 4-digit year as output. 0-49 will return a 20xx year. 50-99 will return a 19xx year. |
| Q | Quarter (1, 2, 3, 4) |
| MM | Month (01, 02, 03, … 12) |
| MON | Abbreviated name of month (Jan, Feb, Mar, … Dec) |
| MONTH | Name of month (January, February, March, … December) |
| RM | Roman number of month (I, II, … XII) |
| WW | Week number (1 – 53) |
| W | Week number of the month (1 – 5) |
| IW | Week number of the ISO year (1 – 52 or 1 – 53) |
| D | Day of week (1 – 7) |
| Day | Day of week in characters (Monday, Tuesday, … Sunday) |
| DD | Day of month (1 – 31) |
| DDD | Day of year (1 – 366) |
| DY | Day of week in short characters (Mon, Tue, … Sun) |
| J | Julian day; the number of days since January 1, 4712 BC. |
| HH, HH12 | Hour number of the day (1 – 12) |
| HH24 | Hour number of the day with 24 hour format (0 – 23) |
| MI | Minute number (0 – 59) |
| SS | Second number (0 – 59) |
| SSSSS | Number of seconds this day(0 – 86399) |
| FF | Fractional seconds. |
| AM, PM | AM or PM |
| AD, A.D | AD |
| BC, B.C | BC |
| TZD | Daylight savings information. e.g, ‘PST’ |
| TZH | Time Zone Hour |
| TZM | Time Zone Minute |
| TZR | Time Zone Region |
ORACLE TO_CHAR Function Example
Numbers Example
SELECT to_char(18.34) FROM dual -- return a string value: 18.34 SELECT to_char(18.34, '99.9') FROM dual -- return a string value: 18.3 SELECT to_char(2118.34, '9,999.9') FROM dual -- return a string value: 2,118.3 SELECT to_char(118, '0009') FROM dual -- return a string value: 0118
Dates Example
SELECT to_char(sysdate) FROM dual -- return a string value: 14-DEC-11 SELECT to_char(sysdate, 'mm/dd/yyyy') FROM dual -- return a string value: 12/14/2011 SELECT to_char(sysdate, 'Mon mm, yy') FROM dual -- return a string value: Dec 12, 11 SELECT to_char(sysdate, 'Mon mm, yyyy hh:mi am') FROM dual -- return a string value: Dec 12, 2011 02:48 AM