Oracle obtains the number of weeks and the start and end dates of each week.

Oracle obtains the number of weeks and the start and end dates of each week.

-- Obtain the start date and end date of the week of the last year, starting from Sunday.
Select
'20140901' | 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 ('1970-01-01 ', 'yyyy-mm-dd'), 'mm') + rownum-1 as wwm from user_objects where rownum <2014) 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 ('1970-01-01 ', 'yyyy-mm-dd'), 'mm') + rownum-1 as wwm from user_objects where rownum <2014) where
To_char (wwm, 'D') = 7) Saturday
Where Sunday. the_week = Saturday. the_week;
 

-- Obtain the start date and end date of the week of the last year, starting from Monday
Select
'20140901' | 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 ('1970-01-01 ', 'yyyy-mm-dd'), 'mm') + rownum-1 as wwm from user_objects where rownum <2014) 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 ('1970-01-01 ', 'yyyy-mm-dd'), 'mm') + rownum-1 as wwm from user_objects where rownum <2014) where
To_char (wwm, 'D') = 7) Sunday
Where Monday. the_week = Sunday. the_week;