TO_CHAR (date conversion) Function In Oracle

     之前小记了一下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