oracle 时间函数

两个日期之间的每个月的最后一天

SELECT distinct last_day(to_date((TO_CHAR(TO_DATE('2009-04-1', 'YYYY-MM-DD') + ROWNUM - 1,
                                'YYYY-MM-DD')),'yyyy-mm-dd'))
  FROM DUAL
CONNECT BY ROWNUM <=
           trunc(to_date('2009-05-23', 'yyyy-mm-dd') -
                 to_date('2009-04-1', 'yyyy-mm-dd')) + 1

 

 

两个月份之间的每个月的指定几号

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('2009-03','YYYY-MM'),ROWNUM-1
),'YYYY-MM')||-15 as DAY_ID
FROM DUAL
CONNECT BY ROWNUM<=months_between
(to_date('2009-07', 'yyyy-mm'),to_date('2009-03', 'yyyy-mm'))+1

 

两个月份之间的所有星期一的日期列表

select tdate
  from (select to_date('2013-04-01', 'yyyy-mm-dd') + rownum - 1 as tdate
          from all_objects
         where rownum <= to_date('2013-05-01', 'yyyy-mm-dd') -
               to_date('2013-04-01', 'yyyy-mm-dd') + 1) t where to_char(tdate, 'day') = '星期一'