How to Return All Sundays between Two Given Dates
Printable View
How to Return All Sundays between Two Given Dates
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')
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;