【TO_CHAR】函数对日期的转换

to_char函数对数字的转换：http://space.itpub.net/?uid-685769-action-viewspace-itemid-742917

to_char的fm：http://space.itpub.net/685769/viewspace-742915

1）scc或cc显示世纪

SYS@ORA11GR2>select to_char(sysdate,'scc yyyy-mm-dd hh24:mi:ss') sys_date from dual;

SYS_DATE

-----------------------

 21 2012-09-08 00:44:53

SYS@ORA11GR2>

2）yyyy显示年

SYS@ORA11GR2>select to_char(sysdate,'yyyy') as yyyy from dual;

YYYY

----

2012

SYS@ORA11GR2>select to_char(sysdate,'y,yyy') as yyyy from dual;

YYYY

-----

2,012

SYS@ORA11GR2>select to_char(sysdate,'yyy') as yyy from dual;

YYY

---

012

SYS@ORA11GR2>select to_char(sysdate,'yy') as yy from dual;

YY

--

12

SYS@ORA11GR2>select to_char(sysdate,'y') as y from dual;

Y

-

2

SYS@ORA11GR2>

3）ISO标准的年IYYY,IYY,IY,I,年的4、3、2、1位值

SYS@ORA11GR2>select to_char(sysdate,'iyyy') as iyyy from dual;

IYYY

----

2012

SYS@ORA11GR2>select to_char(sysdate,'iyy') as iyyy from dual;

IYY

---

012

SYS@ORA11GR2>select to_char(sysdate,'iy') as iy from dual;

IY

--

12

SYS@ORA11GR2>select to_char(sysdate,'i') as i from dual;

I

-

2

SYS@ORA11GR2>

4）syear或year，显示英文拼写的年

SYS@ORA11GR2>select to_char(sysdate,'year') as year from dual;

YEAR

------------------------------------------

twenty twelve

SYS@ORA11GR2>select to_char(sysdate,'syear') as syear from dual;

SYEAR

-------------------------------------------

 twenty twelve

SYS@ORA11GR2>

5）Q，当前时间为一年的第几季度

SYS@ORA11GR2>select to_char(sysdate,'q') as q,to_char(sysdate,'yyyy-mm-dd') as sdate from dual;

Q SDATE

- ----------

3 2012-09-08

SYS@ORA11GR2>

6）mm，显示两位的月份

SYS@ORA11GR2>select to_char(sysdate,'mm') as mm from dual;

MM

--

09

SYS@ORA11GR2>

7）month，9位字符长度（不足9位填充空格）月的名字

SYS@ORA11GR2>select to_char(sysdate,'month') as month from dual;

MONTH

------------------------------------

september

SYS@ORA11GR2>

SYS@ORA11GR2>select to_char(sysdate-30,'month') from dual;

TO_CHAR(SYSDATE-30,'MONTH')

------------------------------------

august

SYS@ORA11GR2>select length(to_char(sysdate-30,'month')) as len from dual;

       LEN

----------

         9

SYS@ORA11GR2>

8）mon，3位缩写字母的月的名字

SYS@ORA11GR2>select to_char(sysdate,'mon') as mon from dual;

MON

------------

sep

SYS@ORA11GR2>

9）ww：一年中的第几周；w：一个月中的第几周

SYS@ORA11GR2>select to_char(sysdate,'ww') as y,to_char(sysdate,'w') as m from dual;

y  m

-- -

36 2

SYS@ORA11GR2>

10）ddd：一年中的第几天；dd：一个月中的第几天；d：一周中的第几天

SYS@ORA11GR2>select to_char(sysdate,'ddd') as y,to_char(sysdate,'dd') as m,to_char(sysdate,'d') as w from dual;

Y   M  W

--- -- -

252 08 7

SYS@ORA11GR2>

注：一周中的第几天，按照如下方式排列

1：周日；

2：周一；

3：周二；

4：周三；

5：周四；

6：周五；

7：周六；

11）day，9位字符长度（不足9位填充空格）天的名字

SYS@ORA11GR2>select to_char(sysdate,'day') as day from dual;

DAY

------------------------------------

saturday

SYS@ORA11GR2>

12）dy，3位字母缩写天的名字

SYS@ORA11GR2>select to_char(sysdate,'dy') as dy from dual;

DY

------------

sat

SYS@ORA11GR2>

13）AM或PM，显示上下午标识

SYS@ORA11GR2>

with t as (select to_date('20120908 100000','yyyymmdd hh24miss') as t1,to_date('20120908 180000','yyyymmdd hh24miss') as t2 from dual) select to_char(t1,'yyyy-mm-dd hh24:mi:ss am') as t1am,to_char(t1,'yyyy-mm-dd hh24:mi:ss pm') as t1pm,       to_char(t2,'yyyy-mm-dd hh24:mi:ss am') as t2am,to_char(t2,'yyyy-mm-dd hh24:mi:ss pm') as t2pm from t;

SYS@ORA11GR2>

T1AM                   T1PM                   T2AM                   T2PM

---------------------- ---------------------- ---------------------- ----------------------

2012-09-08 10:00:00 am 2012-09-08 10:00:00 am 2012-09-08 18:00:00 pm 2012-09-08 18:00:00 pm

14）hh（天的小时）,hh12（1-12）,hh24（0-23）

SYS@ORA11GR2>with t as (select to_date('20120908 22:22:22','yyyymmdd hh24:mi:ss') as dt from dual) select to_char(dt,'yyyy-mm-dd hh:mi:ss') as hh,to_char(dt,'yyyy-mm-dd hh12:mi:ss') as hh12,to_char(dt,'yyyy-mm-dd hh24:mi:ss') as hh24 from t;

HH                  HH12                HH24

------------------- ------------------- -------------------

2012-09-08 10:22:22 2012-09-08 10:22:22 2012-09-08 22:22:22

SYS@ORA11GR2>

15）th，显示序数，如：

SYS@ORA11GR2>select to_char(sysdate,'ddth') as ddth from dual;

DDTH

----

08th

SYS@ORA11GR2>

16）sp，显示英文日期

SYS@ORA11GR2>select to_char(sysdate,'ddsp') as ddth from dual;

DDTH

------------

eight

SYS@ORA11GR2>

17）spth或thsp

SYS@ORA11GR2>select to_char(sysdate,'ddspth') as ddspth,to_char(sysdate,'ddthsp') as ddthsp from dual;

DDSPTH         DDTHSP

-------------- --------------

eighth         eighth

SYS@ORA11GR2>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/685769/viewspace-742918/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/685769/viewspace-742918/