oracle 获取月周日序列

根据指定日期获取改日期下的年周月的日期

select  TRUNC(to_date('2013-09-03','yyyy-MM-dd'),'y')  from dual;---获取当前年，结果为2013-1-1

select  TRUNC(to_date('2013-09-30','yyyy-MM-dd'),'mm')  from dual;---获取当前月，结果为2013-9-1

select  TRUNC(to_date('2013-09-27','yyyy-MM-dd'),'d')+1  from dual;---获取当前周周一的时间，结果为2013-9-23

 

获取指定时间段内的日期序列

--获取时间段内的每天日期
select  to_date('20090101','yyyymmdd')  + level - 1  from dual connect by  level <= to_date('20090630','yyyymmdd') - to_date('20090101','yyyymmdd')+1

   --获取时间段内每周五日期

 select ww from (select distinct (TRUNC(dt,'d')+5 ) ww
 from (
        select  (to_date('2013-01-01', 'yyyy-mm-dd') + rownum - 1) dt
          from dual
        connect by rownum <= to_date('2013-09-30', 'yyyy-mm-dd') -
                   to_date('2013-01-01', 'yyyy-mm-dd') + 1
       
        )

 ) aa order by ww

 

--获取时间段内月日期

 select ww from
(select distinct (TRUNC(dt,'mm') ) ww
  from (
       
        select (to_date('2013-01-01', 'yyyy-mm-dd') + rownum - 1) dt
          from dual
        connect by rownum <= to_date('2013-09-30', 'yyyy-mm-dd') -
                   to_date('2013-01-01', 'yyyy-mm-dd') + 1
       
        )

 ) aa order by ww

另外用month函数获取月日期

select add_months(date'2009-01-01', rownum - 1) d from dual
connect by rownum <= months_between(date'2013-08-01', date'2009-01-01')

 

---获取月的日期，只显示年月

 select distinct(to_char(dt,'yyyymm')) from (

select (to_date('2005-01-01', 'yyyy-mm-dd')+rownum-1) dt from dual
connect by rownum<=to_date('2008-12-30', 'yyyy-mm-dd')-to_date('2005-01-01', 'yyyy-mm-dd') + 1
) 

ORDER BY to_char(dt,'yyyymm')

 

--获取时间段内年日期

 select ww from
(select distinct (TRUNC(dt,'y') ) ww
  from (
       
        select (to_date('2013-01-01', 'yyyy-mm-dd') + rownum - 1) dt
          from dual
        connect by rownum <= to_date('2013-09-30', 'yyyy-mm-dd') -
                   to_date('2013-01-01', 'yyyy-mm-dd') + 1
       
        )

 ) aa order by ww

 

--获取指定日期的周五的日期 利用next_day函数

select next_day(to_date('2013-09-01','yyyy-mm-dd'),'星期五') from  dual