Oracle获取周几以及每周对应得开始日期和结束日

Oracle获取周几以及每周对应得开始日期和结束日期

--获取近一年周的开始日期和结束日期,从星期日开始
 select
 '2014'||sunday.the_week,decode(sign(sunday.the_day-saturday.the_day),-1,sunday.the_day,sunday.the_day-7)
 sunday,saturday.the_day saturday from
 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day from (select
 trunc(to_date('2014-01-01','yyyy-mm-dd'), 'MM')+rownum-1 as wwm from user_objects where rownum < 366) where
 to_char(wwm,'D')=1 ) sunday,
 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day  from (select
 trunc(to_date('2014-01-01','yyyy-mm-dd'), 'MM')+rownum-1 as wwm from user_objects where rownum < 366) where
 to_char(wwm,'D')=7 ) saturday
 where sunday.the_week=saturday.the_week;
 

--获取近一年周的开始日期和结束日期,从星期一开始
 select
 '2014'||monday.the_week,decode(sign(monday.the_day-sunday.the_day),-1,monday.the_day,monday.the_day-6)
 sunday,sunday.the_day sunday from
 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day from (select
 trunc(to_date('2014-01-01','yyyy-mm-dd'), 'MM')+rownum-1 as wwm from user_objects where rownum < 366) where
 to_char(wwm,'D')=1 ) monday,
 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm+1 the_day  from (select
 trunc(to_date('2014-01-01','yyyy-mm-dd'), 'MM')+rownum-1 as wwm from user_objects where rownum < 366) where
 to_char(wwm,'D')=7 ) sunday
 where monday.the_week=sunday.the_week;

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/dd70d60ce13eeca92cbeef958ee78877.html