Return All sundays Between Two Dates

[ashwin123]

How to Return All Sundays between Two Given Dates

[hrishy]

Hi

First you need to generate all the days between given two dates
using a Subquery Factoring and then select only sundays from them



with all_days as
(select to_date('&from_date','mm/dd/yyyy')
+level
-1 all_sundays
from dual
connect by level<=to_date ('&to_date', 'mm/dd/yyyy')
- to_date ('&from_date', 'mm/dd/yyyy')
+ 1)
select all_sundays
from all_days
where to_char(all_sundays, 'DY') IN ('SUN')


[chinnusamys]

declare
l_day DATE;
begin
l_day := to_date('&dd', 'DD-MON-YYYY');
while l_day < to_date('&mm','DD-MON-YYYY') loop
if to_char(to_date(l_day), 'DAY') like 'SUNDAY%' then
dbms_output.put_line(l_day);
end if;
l_day := l_day + 1;
end loop;
end;