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