Select days,week,row_number()over(PARTITION By week Order By days) From (select trunc(sysdate,'month')+rownum - 1 Days,to_char(trunc(sysdate,'month')+rownum - 1,'day')week
from dualconnect by rownum'1') and
(to_char(dayofyear, 'd') < to_char(to_date(&year || '0101', 'yyyymmdd'), 'd'))
then
to_char(to_char(dayofyear, 'ww') + 1, 'fm00')
else
to_char(dayofyear, 'ww')
end as new_yweek,
to_char(dayofyear, 'd') as wday,
to_char(dayofyear, 'dd') as mday
from (select to_date(&year || '0101', 'yyyymmdd') + level - 1 as dayofyear
from dual
connect by level <= to_char(to_date(&year || '1231', 'yyyymmdd'),'ddd')))
group by mon, mon_name, new_yweek |
