oracle时间处理 to_date to_char

SYS@primary/2002-01-01 00:11:39>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2011-04-19 18:45:52

SYS@primary/2011-04-19 18:49:40>select to_char(sysdate,'yyyy yyy yy month mon mm dd ddd dy day ddspth hh24 hh mi ss') from dual;

TO_CHAR(SYSDATE,'YYYYYYYYYMONTHMONMMDDDDDDYDAYDDSPTHHH24HHMISS')
------------------------------------------------------------------------------------------
2011 011 11 april     apr 04 19 109 tue tuesday   nineteenth 18 06 50 21

1 row selected.

select to_char(to_date('2011-04-19','yyyy-mm-dd'),'yyyy') from dual;

TO_C
----
2011

select to_char(to_date('2011-04-19','yyyy-mm-dd'),'yyyy yyy month mon mm') from dual;

TO_CHAR(TO_DATE('2011-04-
-------------------------
2011 011 april     apr 04

>select to_char(to_date('2011-04-19','yyyy-mm-dd'),'day') from dual;

TO_CHAR(T
---------
tuesday

select to_date('2011:04-19','yyyy-mm-dd hh24 mi ss') from dual;

TO_DATE('2011:04-19
-------------------
2011-04-19 00:00:00

select floor(sysdate-to_date('2011 04 01 12 43','yyyy-mm dd hh mi')) from dual;

FLOOR(SYSDATE-TO_DATE('201104011243','YYYY-MMDDHHMI'))
------------------------------------------------------
                                                    18


select round(months_between(to_date('20030118','yyyy-mm-dd'),to_date('20010201','yyyy-mm-dd'))) from dual;

ROUND(MONTHS_BETWEEN(TO_DATE('20030118','YYYY-MM-DD'),TO_DATE('20010201','YYYY-MM-DD')))
----------------------------------------------------------------------------------------
                                                                                      24

SYS@primary/2011-04-19 19:17:50>select round(to_date('20030118','yyyy-mm-dd'),'dd') from dual;

ROUND(TO_DATE('2003
-------------------
2003-01-18 00:00:00

select trunc(to_date('20030118 232443','yyyy-mm-dd HH24:MI:SS'),'dd') from dual;

TRUNC(TO_DATE('2003
-------------------
2003-01-18 00:00:00

select round(to_date('20030118 232443','yyyy-mm-dd HH24:MI:SS'),'dd') from dual;

ROUND(TO_DATE('2003
-------------------
2003-01-19 00:00:00

select round(to_date('20030118 232443','yyyy-mm-dd HH24:MI:SS'),'mm') from dual;

ROUND(TO_DATE('2003
-------------------
2003-02-01 00:00:00

select round(to_date('200818 232443','yy-mm-dd HH24:MI:SS'),'yy') from dual;

ROUND(TO_DATE('2008
-------------------
2021-01-01 00:00:00

1 row selected.

Elapsed: 00:00:00.00
SYS@primary/2011-04-19 19:22:18>select round(to_date('200818 232443','yyyy-mm-dd HH24:MI:SS'),'yy') from dual;
select round(to_date('200818 232443','yyyy-mm-dd HH24:MI:SS'),'yy') from dual
                     *
ERROR at line 1:
ORA-01861: literal does not match format string

select to_char(sysdate,'ddd') from dual;

TO_
---
109

select add_months(to_date('20020123','yyyy-mm-dd'),1) from dual;

ADD_MONTHS(TO_DATE(
-------------------
2002-02-23 00:00:00

select add_months(to_date('20000101','yyyy-mm-dd'),12) - to_date('20000101','yyyy-mm-dd') from dual;

ADD_MONTHS(TO_DATE('20000101','YYYY-MM-DD'),12)-TO_DATE('20000101','YYYY-MM-DD')
--------------------------------------------------------------------------------
                                                                             366

select sysdate,time,TRUNC(time*24) Hours,    
TRUNC(time*24*60 - 60*TRUNC(time*24)) Minutes,    
TRUNC(time*24*60*60 - 60*TRUNC(time*24*60)) Seconds   
  4  from  (select sysdate - trunc(sysdate) time from dual)
  5  ;

SYSDATE                   TIME      HOURS    MINUTES    SECONDS
------------------- ---------- ---------- ---------- ----------
2011-04-19 20:08:53 .839502315         20          8         53


select sysdate S1,
   round(sysdate) S2 ,
   round(sysdate,'year') YEAR,
   round(sysdate,'month') MONTH ,
  5     round(sysdate,'day') DAY from dual;

S1                  S2                  YEAR                MONTH               DAY
------------------- ------------------- ------------------- ------------------- -------------------
2011-04-19 20:11:43 2011-04-20 00:00:00 2011-01-01 00:00:00 2011-05-01 00:00:00 2011-04-17 00:00:00

1 row selected.

Elapsed: 00:00:00.00
select sysdate S1,                   
     trunc(sysdate) S2,
     trunc(sysdate,'year') YEAR,
     trunc(sysdate,'month') MONTH,
     trunc(sysdate,'day') DAY
  6     from dual;

S1                  S2                  YEAR                MONTH               DAY
------------------- ------------------- ------------------- ------------------- -------------------
2011-04-19 20:13:46 2011-04-19 00:00:00 2011-01-01 00:00:00 2011-04-01 00:00:00 2011-04-17 00:00:00

1 row selected.

select to_char(next_day(sysdate,4),'yyyy-mm-dd') from dual;

TO_CHAR(NE
----------
2011-04-20

1 row selected.

select round(months_between(sysdate,to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) from dual;

ROUND(MONTHS_BETWEEN(SYSDATE,TO_DATE('2007-11-0215:55:03','YYYY-MM-DDHH24:MI:SS')))
-----------------------------------------------------------------------------------
                                                                                 42

select to_char(trunc(sysdate,'mm'),'yyyy-mm-dd') first_day,to_char(add_months(trunc(sysdate,'mm'),1)-1,'yyyy-mm-dd') last_day from dual;

FIRST_DAY  LAST_DAY
---------- ----------
2011-04-01 2011-04-30

1 row selected.

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

转载于:http://blog.itpub.net/24890594/viewspace-692914/