Return All sundays Between Two Dates
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Return All sundays Between Two Dates

Hybrid View

  1. #1
    Join Date
    Aug 2007
    Posts
    1

    Return All sundays Between Two Dates

    How to Return All Sundays between Two Given Dates

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    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')
    Last edited by hrishy; 08-22-2007 at 07:30 AM.

  3. #3
    Join Date
    Aug 2007
    Posts
    2
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width