|
select TO_CHAR(saturday.the_day, 'YYYY') ||
TO_CHAR(sunday.the_week, 'fm099') THIS_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(sysdate, '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(sysdate, '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
ORDER BY sunday.the_week
结果
2014001 2013-12-29 2014-1-4
2014002 2014-1-5 2014-1-11
2014003 2014-1-12 2014-1-18
2014004 2014-1-19 2014-1-25
2014005 2014-1-26 2014-2-1
2014006 2014-2-2 2014-2-8
2014007 2014-2-9 2014-2-15
2014008 2014-2-16 2014-2-22
2014009 2014-2-23 2014-3-1
2014010 2014-3-2 2014-3-8
2014011 2014-3-9 2014-3-15
2014012 2014-3-16 2014-3-22
2014013 2014-3-23 2014-3-29
2014014 2014-3-30 2014-4-5
2014015 2014-4-6 2014-4-12
2014016 2014-4-13 2014-4-19
2014017 2014-4-20 2014-4-26
2013018 2013-4-28 2013-5-4
2013019 2013-5-5 2013-5-11
2013020 2013-5-12 2013-5-18
2013021 2013-5-19 2013-5-25
2013022 2013-5-26 2013-6-1
2013023 2013-6-2 2013-6-8
2013024 2013-6-9 2013-6-15
2013025 2013-6-16 2013-6-22
2013026 2013-6-23 2013-6-29
2013027 2013-6-30 2013-7-6
2013028 2013-7-7 2013-7-13
2013029 2013-7-14 2013-7-20
2013030 2013-7-21 2013-7-27
2013031 2013-7-28 2013-8-3
2013032 2013-8-4 2013-8-10
2013033 2013-8-11 2013-8-17
2013034 2013-8-18 2013-8-24
2013035 2013-8-25 2013-8-31
2013036 2013-9-1 2013-9-7
2013037 2013-9-8 2013-9-14
2013038 2013-9-15 2013-9-21
2013039 2013-9-22 2013-9-28
2013040 2013-9-29 2013-10-5
2013041 2013-10-6 2013-10-12
2013042 2013-10-13 2013-10-19
2013043 2013-10-20 2013-10-26
2013044 2013-10-27 2013-11-2
2013045 2013-11-3 2013-11-9
2013046 2013-11-10 2013-11-16
2013047 2013-11-17 2013-11-23
2013048 2013-11-24 2013-11-30
2013049 2013-12-1 2013-12-7
2013050 2013-12-8 2013-12-14
2013051 2013-12-15 2013-12-21
2013052 2013-12-22 2013-12-28
|