Query which gives list of dates between two date ranges

Question

I am sorry for this but my previous question was not properly framed, so creating another post.

My question is similar to following question: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14582643282111

I need to write inner query which will give me a list of dates between two date ranges to outer query.

My inner query returns following 2 rows:

SELECT request.REQ_DATE, request.DUE_DATE FROM myTable where id = 100

REQ_DATE           DUE_DATE
    3/19/2013          3/21/2013 
    3/8/2013           3/8/2013

So I need inner query which will return following dates to outer query:

3/19/2013
3/20/2013
3/21/2013
3/8/2013

The answer in above post has start date and end date hard coded and in my case, it is coming from other table. So I am trying to write query like this which does not work:

Select * from outerTable where  my_date in
(   
    select to_date(r.REQ_DATE) + rownum -1 from all_objects, 
    (   
         SELECT REQ_DATE, DUE_DATE
         FROM    myTable where id = 100
    )  r                              
    where rownum <= to_date(r.DUE_DATE,'dd-mon-yyyy')-to_date(r.REQ_DATE,'dd-mon-yyyy')+1;
)

If the answer below doesn't work for you, please see the answer I left on the prior post. I added the answer just a couple minutes ago, not knowing you'd re-posted. My answer is based on the "Ask Tom" article you referenced. That said, I think the answer below looks solid. — Ed Gibbs, Mar 08 '13 at 21:09

score 0 · Accepted Answer · answered Mar 08 '13 at 20:15

with 
T_from_to as (
   select
      trunc(REQ_DATE) as d_from, 
      trunc(DUE_DATE) as d_to
   FROM myTable 
   where id = 100
),
T_seq as (
   select level-1 as delta
   from dual
   connect by level-1 <= (select max(d_to-d_from) from T_from_to)
)
select distinct d_from + delta
from T_from_to, T_seq
where d_from + delta <= d_to
order by 1

Query which gives list of dates between two date ranges

1 Answers1