--擷取近一年周的開始日期和結束日期，從星期日開始 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;