Tuesday 11 April 2017

Splits Months into Weeks

SELECT START_date, END_DATE , START_date || ' To '|| END_DATE  P3riod
FROM
(
select  decode((trim(to_char(period,'DAY'))), 'MONDAY', period,period) START_date,
                decode((trim(to_char(period,'DAY'))), 'SUNDAY', period ,period + 6) END_date
from
(
select next_day(trunc(:P_DATE-7), 'Monday')+(7*(level-1)) PERIOD
from dual
connect by level <=1000
union all
select next_day(trunc(:P_DATE), 'Sunday')+(7*(level-1))  period
from dual
connect by level <=1000
order by 1
)
where PERIOD <= :T_DATE
)
WHERE  START_date <> END_date

No comments:

Post a Comment

AOL SYLLABUS: PREREQUISITE SQL PL/SQL D2K GENERAL INTRODUCTION KNOW HOW OF ERP Version of Oracle Apps 11i Comparison of 10.7/11...