TO_CHAR function in oracle to convert a date or number to the string format

By Santhosh N

This explains how to convert the the given date or integer values to strings.

There is function by the name, TO_CHAR which is used convert the given date or numeric values to the string format.

Syntax: TO_CHAR (n, [format], [NlsLang])

Here, n is the numeric or date value that’s need to be converted to the string format, format is the format that n needs to presented in,  and NlsLang is the nls languageused to convert the n to a string. The last two parameters are optional here.

Valid formats are as follows:

YEAR       Year, spelled out
YYYY       4-digit year
YYY
YY
Y           Last 3, 2, or 1 digit(s) of year.
IYY
IY
I           Last 3, 2, or 1 digit(s) of ISO year.
IYYY       4-digit year based on the ISO standard
Q           Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM       Month (01-12; JAN = 01).
MON       Abbreviated name of month.
MONTH   Name of month, padded with blanks to length of 9 characters.
RM       Roman numeral month (I-XII; JAN = I).
WW       Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W          Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW      Week of year (1-52 or 1-53) based on the ISO standard.
D          Day of week (1-7).
DAY      Name of day.
DD      Day of month (1-31).
DDD      Day of year (1-366).
DY          Abbreviated name of day.
J          Julian day; the number of days since January 1, 4712 BC.
HH      Hour of day (1-12).
HH12     Hour of day (1-12).
HH24      Hour of day (0-23).
MI          Minute (0-59).
SS          Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF          Fractional seconds.


Ex1: SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM dual;
This returns “May 26, 2010”

Ex2: SELECT TO_CHAR(SYSDATE, 'MON DDth, YYYY') FROM dual;
This returns “MAY 26TH, 2010”

Related FAQs

This explains how to convert the string to date format in oracle.
This explains how to truncate the date to the nearest year, month, date, etc...
This explains how to get the offset value of the specified time zone in oracle.
This explains how to get the next weekday name’s date from the specified date.
This explains how to convert the date and time from one time zone to other and return the equivalent time to desired time zone.
This explains how to get the current date and time in the time zone of the current SQL session which is set by the Alter session command.
TO_CHAR function in oracle to convert a date or number to the string format  (4112 Views)