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

Hi,

That works great. I never would have thought of that.

Thanks!

Ian

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:fveE8.403$DF2.212434_at_twister.socal.rr.com...
> 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 Wed May 15 2002 - 11:35:05 CDT