你是想求天,还是..? 1. Truncates 7:45:26 P.M. on May 25, 2005, to the hour SQL> SQL> --Truncates 7:45:26 P.M. on May 25, 2005, to the hour SQL> SQL> SELECT TO_CHAR(TRUNC(TO_DATE('25-MAY-2005 19:45:26','DD-MON-YYYY HH24:MI:SS'), 'HH24'), 'DD-MON-YYYY HH24:MI:SS') FROM dual;TO_CHAR(TRUNC(TO_DAT -------------------- 25-MAY-2005 19:00:00SQL>2. TRUNC(SYSDATE,'Q'): Truncating to the quarter SQL> -- Truncating to the quarter. SQL> SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'Q'),'MM/DD/YYYY HH:MM:SS AM') "Today's Date and Time" from DUAL;Today's Date and Time ---------------------- 07/01/2006 12:07:00 AMSQL> SQL> SQL>3. ADD_MONTHS(SYSDATE,2.654) SQL> SELECT ADD_MONTHS(SYSDATE,2.654) from DUAL;ADD_MONTH --------- 31-OCT-06SQL> SQL> SQL>4. TRUNC: Removing the time from SYSDATE SQL> SQL> -- Removing the time from SYSDATE. SQL> SQL> SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH:MM:SS AM') "Today's Date and Time" from DUAL;Today's Date and Time ---------------------- 08/31/2006 12:08:00 AMSQL> SQL>
--截取到dd,相减,再乘以24(h) select (trunc(to_date('2011-04-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'dd')- trunc(to_date('2011-04-01 05:32:12','yyyy-mm-dd hh24:mi:ss'),'dd'))*24*60 hours from dual; HOURS ---------- 27360 --1天24h,60miselect 19*24*60 from dual; 19*24*60 ---------- 27360--下面是trunc(dt,format)关于时间的应用: select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'year') dt from dual; DT ----------- 2011-1-1 -- select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'month') from dual; TRUNC(TO_DATE('2011-4-2005:32:' ------------------------------ 2011-4-1 -- select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'dd') from dual; TRUNC(TO_DATE('2011-4-2005:32:' ------------------------------ 2011-4-20 -- select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'hh24') from dual; TRUNC(TO_DATE('2011-4-2005:32:' ------------------------------ 2011-4-20 5:00:00 -- select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'mi') from dual; TRUNC(TO_DATE('2011-4-2005:32:' ------------------------------ 2011-4-20 5:32:00 --参考:Trunc Function (with dates)
1. Truncates 7:45:26 P.M. on May 25, 2005, to the hour
SQL>
SQL> --Truncates 7:45:26 P.M. on May 25, 2005, to the hour
SQL>
SQL> SELECT TO_CHAR(TRUNC(TO_DATE('25-MAY-2005 19:45:26','DD-MON-YYYY HH24:MI:SS'), 'HH24'), 'DD-MON-YYYY HH24:MI:SS') FROM dual;TO_CHAR(TRUNC(TO_DAT
--------------------
25-MAY-2005 19:00:00SQL>2. TRUNC(SYSDATE,'Q'): Truncating to the quarter
SQL> -- Truncating to the quarter.
SQL>
SQL> SELECT TO_CHAR(TRUNC(SYSDATE,'Q'),'MM/DD/YYYY HH:MM:SS AM') "Today's Date and Time" from DUAL;Today's Date and Time
----------------------
07/01/2006 12:07:00 AMSQL>
SQL>
SQL>3. ADD_MONTHS(SYSDATE,2.654)
SQL> SELECT ADD_MONTHS(SYSDATE,2.654) from DUAL;ADD_MONTH
---------
31-OCT-06SQL>
SQL>
SQL>4. TRUNC: Removing the time from SYSDATE
SQL>
SQL> -- Removing the time from SYSDATE.
SQL>
SQL> SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH:MM:SS AM') "Today's Date and Time" from DUAL;Today's Date and Time
----------------------
08/31/2006 12:08:00 AMSQL>
SQL>
--截取到dd,相减,再乘以24(h)
select (trunc(to_date('2011-04-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'dd')-
trunc(to_date('2011-04-01 05:32:12','yyyy-mm-dd hh24:mi:ss'),'dd'))*24*60 hours
from dual;
HOURS
----------
27360 --1天24h,60miselect 19*24*60 from dual;
19*24*60
----------
27360--下面是trunc(dt,format)关于时间的应用:
select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'year') dt from dual;
DT
-----------
2011-1-1
--
select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'month') from dual;
TRUNC(TO_DATE('2011-4-2005:32:'
------------------------------
2011-4-1
--
select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'dd') from dual;
TRUNC(TO_DATE('2011-4-2005:32:'
------------------------------
2011-4-20
--
select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'hh24') from dual;
TRUNC(TO_DATE('2011-4-2005:32:'
------------------------------
2011-4-20 5:00:00
--
select trunc(to_date('2011-4-20 05:32:12','yyyy-mm-dd hh24:mi:ss'),'mi') from dual;
TRUNC(TO_DATE('2011-4-2005:32:'
------------------------------
2011-4-20 5:32:00
--参考:Trunc Function (with dates)