Oracle TO_CHAR Function is used to convert number type or date type into string type. Oracle DATE TO_CHAR query can convert a date type to character type. SQL TO_CHAR Function can be used with SQL SYSDATE also. PLSQL TO_CHAR Function syntax and examples are shown below.
Oracle TO_CHAR Function Syntax
SELECT TO_CHAR(value/date, [FORMAT_MASK],[NLS_LANGUAGE]) FROM table_name;
In the above SQL TO_CHAR Syntax:
- FORMAT_MASK is an optional field, if present; it is used to convert value into string.
- NLS_LANGUAGE is also an optional field, if present; it is used to convert value into string.
Oracle TO_CHAR Function – Converting Number to String Example
Oracle TO_CHAR Function can convert Numbers to String.
Table below shows how SQL TO_CHAR Function converts numbers to string:
| Oracle TO_CHAR Query |
Output |
SELECT TO_CHAR(12345.12345,'99999.999') "PLSQL TO_CHAR" FROM DUAL; |
12345.123 |
SELECT TO_CHAR(12345.12345,'99,999.999') "PLSQL TO_CHAR" FROM DUAL; |
12,345.123 |
SELECT TO_CHAR(12345.12345,'$99,999.999') "PLSQL TO_CHAR" FROM DUAL; |
$12,345.123 |
SELECT TO_CHAR(12345.12345,'$0099,999.999') "PLSQL TO_CHAR" FROM DUAL; |
$0012,345.123 |
SELECT TO_CHAR(12345.12345,'99999.9') "PLSQL TO_CHAR" FROM DUAL; |
12345.1 |
Note: We have aliased every query result as PLSQL TO_CHAR.
Oracle TO_CHAR Function – Oracle DATE TO_CHAR Example
Oracle TO_CHAR Function can also be used with SQL SYSDATE.
Oracle DATE TO_CHAR queries can be written to convert date type to date type.
For example, table below shows Oracle DATE TO_CHAR conversion with SYSDATE as parameter.
| Parameter | Explanation | Oracle DATE TO_CHAR Query |
Output |
| YEAR | Year, spelled out in words |
SELECT TO_CHAR(SYSDATE,'YEAR') FROM DUAL; |
TWENTY TWELVE |
| YYYY | Year in 4 digits |
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL; |
2012 |
| YYY | Year in 3 digits |
SELECT TO_CHAR(SYSDATE,'YYY') FROM DUAL; |
012 |
| YY | Year in 2 digits |
SELECT TO_CHAR(SYSDATE,'YY') FROM DUAL; |
12 |
| Y | Year in 1 digit |
SELECT TO_CHAR(SYSDATE,'Y') FROM DUAL; |
2 |
| IYYY | Year in 4 digits based on ISO standard |
SELECT TO_CHAR(SYSDATE,'IYYY') FROM DUAL; |
2012 |
| IYY | Last 3 digits of year based on ISO standard |
SELECT TO_CHAR(SYSDATE,'IYY') FROM DUAL; |
012 |
| IY | Last 2 digits of year based on ISO standard |
SELECT TO_CHAR(SYSDATE,'IY') FROM DUAL; |
12 |
| I | Last 2 digit of year based on ISO standard |
SELECT TO_CHAR(SYSDATE,'I') FROM DUAL; |
2 |
| Q | Quarter of Year TO_CHAR(1,2,3,4) January-March 1st quarter |
SELECT TO_CHAR(SYSDATE,'Q') FROM DUAL; |
4 |
| MM | Month TO_CHAR(01-12) January=01 |
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL; |
11 |
| MON | Short name for month e.g. NOV |
SELECT TO_CHAR(SYSDATE,'MON') FROM DUAL; |
NOV |
| MONTH | Full name of month e.g. JUNE |
SELECT TO_CHAR(SYSDATE,'MONTH') FROM DUAL; |
NOVEMBER |
| RM | Roman Numeral for month Jan = I, Dec = XII |
SELECT TO_CHAR(SYSDATE,'RM') FROM DUAL; |
XI |
| WW | Week of Year, 01 Jan – 07 Jan 1st week |
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL; |
44 |
| W | Week of month (1-5) starts at 1st – 7th of month is first week |
SELECT TO_CHAR(SYSDATE,'W') FROM DUAL; |
1 |
| IW | Week of Year (1-52) or(1-53) based on ISO standard |
SELECT TO_CHAR(SYSDATE,'IW') FROM DUAL; |
44 |
| D | Day of Week (1-7) |
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL; |
5 |
| DD | Day of Month(1-31) |
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL; |
01 |
| DDD | Day of Year (1-366) |
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; |
306 |
| DAY | Name of Day e.g. MONDAY |
SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL; |
THURSDAY |
| DY | Short name of Day e.g. MON |
SELECT TO_CHAR(SYSDATE,'DY') FROM DUAL; |
THU |
| J | Julian day; the number of days since January 1, 4712 BC. |
SELECT TO_CHAR(SYSDATE,'J') FROM DUAL; |
2456233 |
| HH | Hour of Day (1-12) |
SELECT TO_CHAR(SYSDATE,'HH') FROM DUAL; |
05 |
| HH12 | Hour of Day (1-12) |
SELECT TO_CHAR(SYSDATE,'HH12') FROM DUAL; |
05 |
| HH24 | Hour of Day (0-23) |
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL; |
17 |
| MI | Minute (0-59) |
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL; |
59 |
| SS | Seconds (0-59) |
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL; |
41 |
| SSSS | Seconds past midnight (0-86399) |
SELECT TO_CHAR(SYSDATE,'SSSS') FROM DUAL; |
0000 |