Oracle 日期时间运算

---------------------oracle 时间运算-------------------------------------------------

--两个日期间的天数
SELECT FLOOR(SYSDATE - TO_DATE('20110701', 'yyyymmdd')) FROM DUAL;

--查找2002-02-28至2002-02-01间除星期一和七的天数
SELECT COUNT(*)
  FROM (SELECT ROWNUM - 1 RNUM
          FROM ALL_OBJECTS
         WHERE ROWNUM <= TO_DATE('2002-02-28', 'yyyy-mm-dd') -
               TO_DATE('2002-02-01', 'yyyy-mm-dd') + 1)
 WHERE TO_CHAR(TO_DATE('2002-02-01', 'yyyy-mm-dd') + RNUM - 1, 'D') NOT IN
       ('1', '7');

--两个日期间的月数
SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('05-21-2011', 'MM-DD-YYYY')) "MONTHS"
  FROM DUAL;

SELECT MONTHS_BETWEEN(SYSDATE, DATE '2010-07-07') MONTHS FROM DUAL;

--处理月份天数不定的办法  
SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(SYSDATE) + 1, -2), 'yyyymmdd'),
       LAST_DAY(SYSDATE)
  FROM DUAL;

--找出今年的天数  
SELECT ADD_MONTHS(TRUNC(SYSDATE, 'year'), 12) - TRUNC(SYSDATE, 'year')
  FROM DUAL;

--闰年的处理方法 ;如果是28就不是闰年 
TO_CHAR(LAST_DAY(TO_DATE('02' || :YEAR, 'mmyyyy')), 'dd')

--不同时区的处理  
  SELECT TO_CHAR(NEW_TIME(SYSDATE, 'GMT', 'EST'), 'dd/mm/yyyy hh:mi:ss'),
         SYSDATE
    FROM DUAL;

--5秒钟一个间隔,SSSSS表示5位秒数  
SELECT TO_DATE(FLOOR(TO_CHAR(SYSDATE, 'SSSSS') / 300) * 300, 'SSSSS'),
       TO_CHAR(SYSDATE, 'SSSSS')
  FROM DUAL;

-- 一年的第几天  
SELECT TO_CHAR(SYSDATE, 'DDD'), SYSDATE FROM DUAL;

--计算小时,分,秒,毫秒  
SELECT DAYS,
       A,
       TRUNC(A * 24) HOURS,
       TRUNC(A * 24 * 60 - 60 * TRUNC(A * 24)) MINUTES,
       TRUNC(A * 24 * 60 * 60 - 60 * TRUNC(A * 24 * 60)) SECONDS,
       TRUNC(A * 24 * 60 * 60 * 100 - 100 * TRUNC(A * 24 * 60 * 60)) MSECONDS
  FROM (SELECT TRUNC(SYSDATE) DAYS, SYSDATE - TRUNC(SYSDATE) A FROM DUAL);

SELECT SUBSTR('2004-32', 1, 4) YY, TO_NUMBER(SUBSTR('2004-32', 6)) WW
  FROM DUAL;

--1.查询某周的第一天
SELECT TRUNC(DECODE(WW,
                    53,
                    TO_DATE(YY || '3112', 'yyyyddmm'),
                    TO_DATE(YY || '-' || TO_CHAR(WW * 7), 'yyyy-ddd')),
             'd') LAST_DAY
  FROM (SELECT SUBSTR('2004-32', 1, 4) YY,
               TO_NUMBER(SUBSTR('2004-32', 6)) WW
          FROM DUAL);

SELECT TRUNC(TO_DATE(SUBSTR('2011-01', 1, 5) ||
                     TO_CHAR((TO_NUMBER(SUBSTR('2011-01', 6))) * 7),
                     'yyyy-ddd'),
             'd') - 6 FIRST_DAY
  FROM DUAL;

SELECT MIN(V_DATE)
  FROM (SELECT (TO_DATE('200201', 'yyyymm') + ROWNUM) V_DATE
          FROM ALL_TABLES
         WHERE ROWNUM < 370)
 WHERE TO_CHAR(V_DATE, 'yyyy-iw') = '2002-49';

--2.查询某周的最后一天
SELECT TRUNC(DECODE(WW,
                    53,
                    TO_DATE(YY || '3112', 'yyyyddmm'),
                    TO_DATE(YY || '-' || TO_CHAR(WW * 7), 'yyyy-ddd')),
             'd') - 6 FIRST_DAY
  FROM (SELECT SUBSTR('2004-33', 1, 4) YY,
               TO_NUMBER(SUBSTR('2004-33', 6)) WW
          FROM DUAL);

SELECT TRUNC(TO_DATE(SUBSTR('2003-01', 1, 5) ||
                     TO_CHAR((TO_NUMBER(SUBSTR('2003-01', 6))) * 7),
                     'yyyy-ddd'),
             'd') LAST_DAY
  FROM DUAL;

SELECT MAX(V_DATE)
  FROM (SELECT (TO_DATE('200408', 'yyyymm') + ROWNUM) V_DATE
          FROM ALL_TABLES
         WHERE ROWNUM < 370)
 WHERE TO_CHAR(V_DATE, 'yyyy-iw') = '2004-33';

--3.查询某周的日期
SELECT MIN_DATE, TO_CHAR(MIN_DATE, 'day') DAY
  FROM (SELECT TO_DATE(SUBSTR('2004-33', 1, 4) || '001' + ROWNUM - 1,
                       'yyyyddd') MIN_DATE
          FROM ALL_TABLES
         WHERE ROWNUM <= DECODE(MOD(TO_NUMBER(SUBSTR('2004-33', 1, 4)), 4),
                                0,
                                366,
                                365)
        UNION
        SELECT TO_DATE(SUBSTR('2004-33', 1, 4) - 1 ||
                       DECODE(MOD(TO_NUMBER(SUBSTR('2004-33', 1, 4)) - 1, 4),
                              0,
                              359,
                              358) + ROWNUM,
                       'yyyyddd') MIN_DATE
          FROM ALL_TABLES
         WHERE ROWNUM <= 7
        UNION
        SELECT TO_DATE(SUBSTR('2004-33', 1, 4) + 1 || '001' + ROWNUM - 1,
                       'yyyyddd') MIN_DATE
          FROM ALL_TABLES
         WHERE ROWNUM <= 7)
 WHERE TO_CHAR(MIN_DATE, 'yyyy-iw') = '2004-33';
 
 
 ---------------------------转换函数--------------------------------------------------------
to_char() --

to_date() --

to_number('numbervalue') --转换成数值

SELECT to_number('1314') FROM dual;

round('numbervalue') --取整函数(四舍五入)

Select round(to_number('123.567')) from dual;

floor(sysdate - to_date('20110701','yyyymmdd'))