Oracle To_char () Functional usage Detailed

The oracle/plsql of the To_char function converts a number or date to a string.

The syntax for the TO_CHAR function is:
To_char (value, [Format_mask], [nls_language])

The value can be a number or the date will be converted to a string.

Format_mask is optional. This is the format that will be used to convert to a string value.

Nls_language is optional. This is the language of the non-income review loan scheme, which is used to convert to a string value.

Applies To:

The code is as follows	Copy Code
Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

Instance

The code is as follows	Copy Code
To_char (1210.73, ' 9999.9 ') would return ' 1210.7 ' To_char (1210.73, ' 9,999.99 ') would return ' 1,210.73 ' To_char (1210.73, ' $9,999.00 ') would return ' $1,210.73 ' To_char (' 000099 ') would return ' 000021 '

Date instance

The following is a valid argument list To_char function is the date that is used to convert to a string. These parameters can be used in a variety of combinations.

Parameter	explanation
Year	Year, the spelled out
YYYY	4-digit year
YYY Yy Y	Last 3, 2, or 1 digit (s) of the year.
Iyy IY I	Last 3, 2, or 1 digit (s) of the 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; A-A-01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
Rm	Roman numeral month (I-XII; A.
Ww	Week of year (1-53) where Week 1 starts on the "the" and "continues to" the "seventh" of the year.
W	Week of month (1-5) where Week 1 starts on the "the" 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 the 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.

The following are date examples for the To_char function.

The code is as follows

Copy Code

To_char (sysdate, ' yyyy/mm/dd '); Would return ' 2003/07/09 ' To_char (sysdate, ' Month DD, YYYY '); would return ' July 09, 2003 ' To_char (sysdate, ' Fmmonth DD, YYYY '); Would return ' July 9, 2003 ' To_char (sysdate, ' MON ddth, YYYY '); Would return ' June 09TH, 2003 ' To_char (sysdate, ' Fmmon ddth, YYYY '); Would return ' June 9TH, 2003 ' To_char (sysdate, ' Fmmon ddth, YYYY '); Would return ' June 9th, 2003 '

You will find, in some cases, that the Format_mask parameter starts with "FM". This means that 0 and spaces are suppressed. This can be seen in the following example.

code is as follows	copy code
To_char ( Sysdate, ' Fmmonth DD, YYYY '); Would return ' July 9, 2003 ' To_char (sysdate, ' Fmmon ddth, YYYY '); would return ' June 9TH, 2003 ' To_char (sysdate, ' Fmmon ddth, YYYY '); Would return ' June 9th, 2003 '