1、获得当年一年的全部日期:
SELECT TRUNC(SYSDATE, 'YY') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= 365;
获取当期日前往前推1年到现在的所有日期:
SELECT ADD_MONTHS(SYSDATE,-12) + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= 365;
获取当前日期往前推12个月的所有年月
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,ROWNUM - 13),'YYYYMM') FROM DUAL CONNECT BY ROWNUM <= 12
2、获得当年一月的全部日期:
SELECT TRUNC(SYSDATE, 'YY') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= 31;
3、获得2018年10月的全部日期:
SELECT TRUNC(to_date('2018-10-09','yyyy-mm-dd'), 'MM') + ROWNUM - 1 FROM DUAL CONNECT BY ROWNUM <= TO_NUMBER(TO_CHAR(LAST_DAY(to_date('2018-10-09','yyyy-mm-dd')), 'DD'));
4、查询星期几:
select to_char(sysdate,'day') day from dual;
select to_char(sysdate, 'DY') from dual;
5、整年的非周末日期:
select dy, to