TO_CHAR (date conversion) Function In Oracle

香5843

　　     之前小记了一下SimpleDateFormat学习心得，由于数据库中也会用到日期格式化，并且两种“Date Format Specifiers——日期格式化定义”稍有不同，所以查了资料，区分开来以便以后速查。
　　 
　　     SQL中关于日期的查询显示常会用到TO_CHAR()函数来格式化Date、TIMESTAMP等日期类型字段。
　　

TO_CHAR (date conversion) Function的定义：

　　TO_CHAR converts date of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, date is converted to a VARCHAR2 value as follows:

• DATE is converted to a value in the default date format.
   
  
• TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE are converted to values in the default timestamp format.
   
  
• TIMESTAMP WITH TIME ZONE is converted to a value in the default timestamp with time zone format.
  

TO_CHAR()函数语法：
　　
　　>──TO_CHAR───(date─┬───────────────────────┬─)──><
                                             └─, fmt───────────────────┘

日期格式化定义：
　　


Format Model
Description


CC, SCC
Century (S prefixes BC dates with a minus sign)


YYYY, SYYYY
Year (S prefixes BC dates with a minus sign)


IYYY
Year based on ISO standard


YYY, YY, Y
Last three, two or one digits of the year


IYY, IY, I
Last three, two or one digits of the ISO year


Y,YYY
(Four Y's with comma) put a comma in the year (1,995)


YEAR, SYEAR
Year spelled out (S prefixes BC dates with a minus sign)


RR
Last two digits of year in another century (allows for year 2000)


BC, AD
BC or AD indicator


B.C., A.D.
BC or AD indicators with periods


Q
Numeric quarter of the year (1-4 with Jan-Mar=1)


MM
2 number month (Jan = 01)


RM
Roman numeral month


MONTH
Name of month spelled out (upper case - month is upper case)


MON
abbreviated name of month (upper case - month is upper case)


WW
Week of the year (1-53)


IW
Week of the year (1-52, 1-53) based on ISO standard


W
Week of month (1-5)


DDD
day of year (1-366) (Don't forget leap years)


DD
day of month (1-31)


D
day of week (1-7)


DAY
Name of day (upper case, day is upper case)


DY
Abbreviated name of day


J
Julian day (Number of days since January 1, 4712 BC)


AM,PM
Meridian indicator


A.M., P.M.
Meridian indicator with periods.


HH, HH12
Hour of day (0-12)


HH24
Use 24 hour clock for hours (1-24)


MI
Minute (0-59)


SS
Second (0-60)


SSSSS
(five S's) seconds past midnight. (0-86399)


None
Date must be in the format 'DD-MON-YY';
　　

　　
　　
　　
　　　　
　　其中，标红的MI我在一次代码中写成了MM，因此我查出的日期都是错误的，我与SimpleDateFormat中的格式定义记混了。
　　在SimpleDateFormat格式化定义中, MM代表分；
　　在Oracle格式化定义中，MM则代表两位月份；
　　除此之外，还存在其他不同的格式化定义，具体可查资料对比；
　　 
　　下面举些例子供参考：
　　select to_char(sysdate,'YYYY/MM/DD') FROM DUAL; -- 2010/05/26


　　select to_char(sysdate,'YYYY') FROM DUAL; -- 2010


　　select to_char(sysdate,'YYY') FROM DUAL; -- 010


　　select to_char(sysdate,'YY') FROM DUAL; -- 10


　　select to_char(sysdate,'MM') FROM DUAL; -- 05


　　select to_char(sysdate,'DD') FROM DUAL; -- 26


　　select to_char(sysdate,'D') FROM DUAL; -- 4


　　select to_char(sysdate,'DDD') FROM DUAL; -- 148


　　select to_char(sysdate,'WW') FROM DUAL; -- 22


　　select to_char(sysdate,'W') FROM DUAL; -- 4


　　select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') FROM DUAL; -- 2010/05/26 15:24:13


　　select to_char(sysdate,'YYYY/MM/DD HH:MI:SS') FROM DUAL; -- 2010/05/26 03:25:23


　　select to_char(sysdate,'J') FROM DUAL; -- 2455345


　　select to_char(sysdate,'RR/MM/DD') FROM DUAL; -- 10/05/28