Introduction:

In this tutorial we are going to explain how to use Oracle TO_CHAR function with basic syntax and many examples for better understanding.

TO_CHAR function convert number and date to string .

Syntax:

TO_CHAR(value, [format_mask], [nls_parameter] )

• value : A number or date to be converted to string.
• format_mask : The format that we can use to convert value to string.
• nls_parameter : nls_lang specified to convert value to string in particular language.

Notes:

• If format_mask parameter is omitted, then Oracle TO_CHAR function convert DATE into character in the default date format mask.
• If format_mask parameter is omitted, then Oracle TO_CHAR function convert TIMESTAMP and TIMESTAMP WITH LOCAL TIMEZONE into character in the default timestamp format mask.
• If format_mask parameter is omitted, then Oracle TO_CHAR function convert TIMESTAMP WITH TIME ZONE into character in the default time zone format mask

Format mask values:

Parameter	Explanation
YEAR	Year, spelled out in full words
YYYY	4-digit year
YYY	Last 3 digits of year
YY	Last 2 digits of year
Y	Last digit of year
IYY	Last 3 digits of ISO year
IY	Last 2 digits of ISO year
I	Last digit of ISO year
IYYY	4-digit year, which is based on the ISO standard
RRRR	This format accepts a 2-digit year, and returns a 4-digit year. If the provided value is between 0 and 49, it will return a year greater than or equal to 2000. If the provided value is between 50 and 99, it will return a year less than 2000
Q	Quarter of year, from 1 to 4. JAN to MAR = 1
MM	Month, from 01 to 12. JAN = 01
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month, from I to XII. JAN = I.
WW	Week of year, from 1 to 53. Week 1 starts on the first day of the year, and continues to the seventh day of the year.
W	Week of month, from 1 to 5. Week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year, from 1 to 52 or 1 to 53, based on the ISO standard.
D	Day of week, from 1 to 7.
DD	Day of month, from 1 to 31.
DDD	Day of year, from 1 to 366.
DY	Abbreviated name of day.
DAY	Name of day.
J	Julian day, which is the number of days since January 1, 4712 BC.
HH	Hour of day, from 1 to 12.
HH12	Hour of day, from 1 to 12.
HH24	Hour of day, from 0 to 23.
MI	Minute, from 0 to 59
SS	Second, from 0 to 59
SSSS	Seconds past midnight, from 0 to 86399.
FF	Fractional seconds. This uses a value from 1 to 9 after FF, to indicate the number of digits in the fractional seconds (e.g. FF7)
AM, A.M., PM, or P.M.	Meridian indicator

Applies to::

Oracle 19c, Oracle 18c, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Examples:

Let’s take some examples about Oracle TO_CHAR and how to use it:

SELECT TO_CHAR(5340.86, '9,9999.99')
FROM DUAL;

SELECT TO_CHAR(5340.86, '9999.9')
FROM   DUAL;

SELECT TO_CHAR(5340.86, '$9,999.00')
FROM   DUAL;

SELECT TO_CHAR(5340.86, '000000000')
FROM   DUAL;

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD')
FROM DUAL;

SELECT TO_CHAR( sysdate, 'DL', 'NLS_DATE_LANGUAGE = ENGLISH')
FROM DUAL;

SELECT TO_CHAR( sysdate, 'Q')
FROM DUAL;

SELECT TO_CHAR(sysdate, 'Month DD, YYYY', 'NLS_DATE_LANGUAGE = ENGLISH')
FROM DUAL;

SELECT TO_CHAR(sysdate, 'FMMON DDth, YYYY', 'NLS_DATE_LANGUAGE = ENGLISH')
FROM DUAL;

Similar oracle functions:

TO_DATE – converts a string value to a DATE type.
TO_NUMBER – converts a value to a NUMBER type
CAST – used for many types, including dates

Hope this help.