1、查询某时间段内日期列表
select level,to_char(to_date(‘2013-12-31‘,‘yyyy-mm-dd‘)+level-1,‘yyyy-mm-dd‘) as date_time from dual connect by level <=to_date(‘2014-01-03‘,‘yyyy-mm-dd‘)-to_date(‘2013-12-31‘,‘yyyy-mm-dd‘)+1
2、查询前两个月日期列表
1 SELECT T.DATE_ID FROM ( 2 select to_char(ADD_MONTHS( TO_DATE(to_char(sysdate-1,‘yyyyMMdd‘),‘yyyyMMdd‘ ),- 2)+ rownum,‘yyyyMMdd‘ ) DATE_ID 3 from dual 4 connect by rownum <=to_date(to_char(sysdate-1,‘yyyyMMdd‘)-ADD_MONTHS( TO_DATE(to_char(sysdate-1,‘YYYYMMDD‘ ),- 2 ) 5 ) t order by DATE_ID desc
3.查询月份列表
1 SELECT TO_CHAR(ADD_MONTHS(TO_DATE(‘2014-10‘,‘yyyy-MM‘),ROWNUM - 1),2 ‘yyyyMM‘) as monthlist 3 FROM DUAL 4 CONNECT BY ROWNUM <= 5 months_between(to_date(‘2015-06‘,‘yyyy-MM‘),6 to_date(‘2014-10‘,‘yyyy-MM‘)) + 1
4.年份列表
1 SELECT 2 TO_CHAR( ADD_MONTHS( TO_DATE( ‘2014-10‘,‘yyyy-MM‘ ),( ROWNUM - 1 ) * 12 ),‘yyyy‘ ) AS yearlist 3 FROM 4 DUAL CONNECT BY ROWNUM <= months_between( 5 to_date( 6 ‘2015-06‘, 7 ‘yyyy-MM‘ 8 ), 9 to_date( 10 ‘2014-10‘,11 ‘yyyy-MM‘ 12 ) 13 ) / 12 + 1
