Re: Select particular day of week over given date range?

Like Daniel says, it's probably more logical to do this in PL/SQL if you can. If you have to have SQL ...

select next_day(to_date(:start_date, 'DD-MON-YYYY'), 'friday') + (rownum - 1) * 7
from user_objects
where rownum <= (next_day(to_date(:end_date, 'DD-MON-YYYY') - 6, 'friday') -
  next_day(to_date(:start_date, 'DD-MON-YYYY'), 'friday')) / 7 + 1

Note: you need to select from a table that has at least as many rows as there will be fridays.

Richard

Ian Brandt wrote:
>
> Hi,
>
> I'm trying to do something like...
>
> select [every friday] from dual
> where [every friday] >= to_date('[start date]', 'MM-DD-YYYY')
> and [every friday] <= to_date('[end date]', 'MM-DD-YYYY')
>
> In other words, I'm trying to dynamically generate a list of dates for
> a particular day of the week, that fall within a given range, out of
> thin air. I see that I can use the next_day function to get the first
> occurrence of the particular day of the week in the given range, and
> if I could some how call that recursively until the result is <= the
> end date I'd be all set. Is it possible to do that in SQL, or is
> there an alternative strategy that is, or do I need to teach myself
> PL/SQL to do something like this?
>
> Thanks!
>
> Ian
Received on Tue May 14 2002 - 15:32:11 CDT