RE: SQL: Listing the dates for a particular day of the week in a range
- From: Anthony Ballo <aballo@xxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Dec 2014 16:56:05 +0000
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@xxxxxxxxx]
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@xxxxxxx<mailto:aballo@xxxxxxx>>
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
Other related posts:
