RE: SQL: Listing the dates for a particular day of the week in a range
- From: Stéphane Faroult <sfaroult@xxxxxxxxxxxx>
- To: "Oracle-L (E-mail)" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 17 Dec 2014 08:38:48 -0600
From: Anthony Ballo<aballo@xxxxxxx>
Subject: SQL: Listing the dates for a particular day of the week in a range
Date: Tue, 16 Dec 2014 19:51:34 +0000
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
Anthony,
Hmmm, that looks like class schedules :-).
First thing is generating all dates for (presumably) the Spring term.
select trunc(start_date) + x.n as the_day
from (select rownun - 1 n
from dual
connect by level < (end_date - start_date)) x
From there you can use something like
case to_char(the_day, 'DY', 'NLS_DATE_LANGUAGE=American')
when 'MON' then 'M'
when 'TUE' then 'T'
when 'WED' then 'W'
when 'THU' then 'R'
when 'FRI' then 'F'
else 'X'
end
to match your weekday format.
Beware of the Spring break, though ...
HTH
Stéphane Faroult
--
//www.freelists.org/webpage/oracle-l
Other related posts:
