Oracle/PLSQL: To_Char Function[转贴]

from: http://www.techonthenet.com/oracle/functions/to_char.htm

Oracle/PLSQL: To_Char Function

---

In Oracle/PLSQL, 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] )

value can either be a number or date that will be converted to a string.

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

nls_language is optional.  This is the nls language used to convert value to a string.

 

Examples - Numbers

The following are number examples for the to_char function.

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 (21, '000099')would return '000021'

 

Examples - Dates

The following is a list of valid parameters when the to_char function is used to convert a date to a string.  These parameters can be used in many combinations.

ParameterExplanationYEARYear, spelled outYYYY4-digit yearYYY
YY
YLast 3, 2, or 1 digit(s) of year.IYY
IY
ILast 3, 2, or 1 digit(s) of ISO year.IYYY4-digit year based on the ISO standardQQuarter of year (1, 2, 3, 4; JAN-MAR = 1).MMMonth (01-12; JAN = 01).MONAbbreviated name of month.MONTHName of month, padded with blanks to length of 9 characters.RMRoman numeral month (I-XII; JAN = I).WWWeek of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.WWeek of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.IWWeek of year (1-52 or 1-53) based on the ISO standard.DDay of week (1-7).DAYName of day.DDDay of month (1-31).DDDDay of year (1-366).DYAbbreviated name of day.JJulian day; the number of days since January 1, 4712 BC.HHHour of day (1-12).HH12Hour of day (1-12).HH24Hour of day (0-23).MIMinute (0-59).SSSecond (0-59).SSSSSSeconds past midnight (0-86399).FFFractional seconds.

 

The following are date examples for the to_char function.

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 'JUL 09TH, 2003'to_char (sysdate, 'FMMON DDth, YYYY');would return 'JUL 9TH, 2003'to_char (sysdate, 'FMMon ddth, YYYY');would return 'Jul 9th, 2003'

 

You will notice that in some examples, the format_mask parameter begins with "FM".  This means that zeros and blanks are suppressed.  This can be seen in the examples below.

to_char (sysdate, 'FMMonth DD, YYYY');would return 'July 9, 2003'to_char (sysdate, 'FMMON DDth, YYYY');would return 'JUL 9TH, 2003'to_char (sysdate, 'FMMon ddth, YYYY');would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

   

Frequently Asked Questions

---

Question:  Why doesn't this sort the day's of the week in order?

select ename, hiredate, to_char((hiredate),'fmDay') "Day" 
from emp
order by "Day";

Answer:  

The fmDay parameter will return the name of the Day and not the numeric value of the day.
Try the following:

select ename, hiredate, to_char((hiredate),'fmDD') "Day" 
from emp 
order by "Day";