RE: SQL: Listing the dates for a particular day of the week in a range

From: Anthony Ballo <aballo_at_unm.edu>
Date: Wed, 17 Dec 2014 16:56:05 +0000
Message-ID: <DM2PR0701MB7164BD051D4073B404F5C02DB6D0_at_DM2PR0701MB716.namprd07.prod.outlook.com>

This is what I have so far:

SELECT LISTAGG(dt,'|') WITHIN GROUP (order by dt) as x1 FROM ( SELECT B.DY, B.dt FROM
(with t as (select date '2015-01-01' as start_date, date '2015-01-31' end_date from dual) select dt, to_char(dt,'fmDay') dy
  from (

        select start_date+rownum-1 dt
        from t
        connect by rownum <= (end_date-start_date)+1
       )) B where DY = 'Monday'
       ) group by dy

Thinking it would form the basis in a Function ? When I try to compile in a Function, the compiler errs on:

  (with t as (select date p_STARTDATE as start_date, date p_ENDDATE end_date from dual)

Is this not compatible?

From: Ronan Merrick [mailto:merrickronan1_at_gmail.com] Sent: Tuesday, December 16, 2014 1:26 PM To: Anthony Ballo
Cc: oracle-l
Subject: Re: SQL: Listing the dates for a particular day of the week in a range

Hi Anthony,

Do you mean if start date was say, 1st December and end date was say 31st December and days was M, you want to return the dates of all the Mondays in that set?

Ronan
On 16 Dec 2014 19:52, "Anthony Ballo" <aballo_at_unm.edu<mailto:aballo_at_unm.edu>> wrote: Hello,

I'm working on a scheduling SQL problem. I have a rowset that returns "days" with a "startdate" and "enddate".

For example - "days" can have the following values:

Days

---

M
T
W
TR
MWF Where: M = Monday, TR = Tuesday & Thursday and MWF = Monday, Wednesday and Friday.

What is the best approach to list the appropriate dates of these days between the "startdate" and "enddate" range? I would mention that "startdate" and "enddate" are the same values for all rows in the rowset if that makes a difference.

Any ideas ?

Thanks,

Anthony

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 17 2014 - 17:56:05 CET