to_char与trunc函数 比较区别

出处：[url]http://blog.sina.com.cn/s/blog_6e9fae1301011slj.html[/url]

一、to_char函数

Parameter Explanation
YEAR Year spelled out
YYYY 4 digits of year
YYY 3 digits of year
YY 2 digits of year
Y 1 digit of year
IYYY 4digits year based on the ISO standard
IYY 3 digits of ISO year
IY 2 digits of ISO year
I 1 digit of ISO year
Q Quarter of year (1 .. 4)
MM Month (01 ..12)
MON Abbreviated name of month
MONTH Name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I .. XII)
WW Week of year (1-53) where 7 days 1 week (与星期几无关)
W Week of month (1-5) where 7 days 1 week (与星期几无关)
IW Week of year (1-52 or 1-53) based on the ISO standard.
(周一到周日为一周，若1日为周五-周日，则为上年最后一周)
D Day of week (周日1 .. 周六7)
DY Abbreviated name of day.
DAY Name of day
DD Day of month (1-31)
DDTH Day of month (1-31)
DDD Day of year (1-366)
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.
XXXXX 转换为8进制

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'
to_char(21, '999999') would return ' 21'
to_char(21, 'FM999999') would return '21'
to_char(sysdate, 'FMYYY') would return '8' --FM表示去掉0或空格

to_char(125, 'XXXXX') would return '7D'
to_number('7D','XXXXX') would return '125'


另注：trunc与to_char的比较

trunc原意为截取数据小数部分，例如：

trunc(23.48429387) 返回23
trunc(23.48429387,3) 返回23.484
trunc(-1.443432) 返回-1

但trunc(date) 具有与to_char(date) 相似的功能，但有区别：

trunc(sysdate,'cc') 取当世纪的第一天 to_char(sysdate,'cc') 取当世纪数值
trunc(sysdate,'yyyy') 取当年的第一天 to_char(sysdate,'yyyy') 取当年数值
trunc(sysdate,'iyyy') 取上年的最后一天 to_char(sysdate,'iyyy') 取当年数值
trunc(sysdate,'q') 取当季第一天 to_char(sysdate,'iyyy') 取当季数值
trunc(sysdate,'mm') 取当月第一天 to_char(sysdate,'mm') 取当月数值
trunc(sysdate,'ww') 取当周第一天(周二) to_char(sysdate,'ww') 取当周数值(第几周)
trunc(sysdate,'iw') 取当周第一天(周一) to_char(sysdate,'iw') 取当周数值(第几周)