常见Oracle日期处理问题

来源：互联网发布：防火墙udp端口4950 编辑：程序博客网时间：2024/06/06 17:19

查询今天是星期几

 SELECT TO_CHAR(SYSDATE, 'D') INTO W_D FROM DUAL; --1表示星期天, 7表示星期六 SELECT TO_CHAR(SYSDATE, 'DAY') INTO W_D FROM DUAL;

日期截取

SELECT SYSDATE, --2017/4/2 13:58:12TRUNC(SYSDATE, 'yyyy'),--2017/1/1TRUNC(SYSDATE, 'mm'),--2017/4/1TRUNC(SYSDATE, 'dd'),--2017/4/2TRUNC(SYSDATE),--2017/4/2TRUNC(SYSDATE, 'hh24'),--2017/4/2 13:00:00TRUNC(SYSDATE, 'mi')--2017/4/2 13:58:00FROM DUAL;

每个月的最后一天

SELECT LAST_DAY(SYSDATE) FROM DUAL;

月天数查询

SELECT TO_CHAR(LAST_DAY(SYSDATE), 'DD') FROM DUAL;

年天数查询

SELECT SUM(TO_CHAR(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'), LEVEL - 1)) , 'DD'))TOTAL_DAYS_OF_YEAR FROM DUAL CONNECT BY LEVEL <= 12;SELECT LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE, 'YYYY'), 11)) - TRUNC(SYSDATE, 'YYYY') + 1 TOTAL_DAYS_OF_YEAR FROM DUAL;

得到一段时间内的所有周末日期

DROP TABLE WEEKEND;CREATE TABLE WEEKEND(WEEK_END DATE);DECLARE  W DATE := TO_DATE('2016-12-01','YYYY-MM-DD');  W_D CHAR;  BEGIN    LOOP      SELECT TO_CHAR(W, 'D') INTO W_D FROM DUAL;      IF W_D = 1 OR W_D = 7 THEN        INSERT INTO WEEKEND VALUES (W);      END IF;      W := W + 1;      IF TO_CHAR(W,'YYYY-MM-DD') = '2016-12-30' THEN        EXIT;      END IF;    END LOOP;  END;  /

0 0