Oracle PL/SQL TO_CHAR Function

Oracle PLSQL TO_CHAR FunctionOracle 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

Tagged , , , , , , , , , , , . Bookmark the permalink.