trunc与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原意为截取数据小数部分，例如：

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')   取当周数值(第几周)

 

转载于:https://www.cnblogs.com/mickeygirl/archive/2013/03/22/2975727.html