Oracleto_char()函数的使用方法

文章作者 100test 发表时间 2007:10:24 13:15:05
来源 100Test.Com百考试题网

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’


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.
ParameterExplanation
YEARYear, spelled out
YYYY4-digit year
YYY
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 standard
QQuarter 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".