ORACLE TO_CHAR

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>