Query which will give a list of dates between two date ranges

Question

My question is similar to following question:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:14582643282111

The difference is my inner query returns two records and I have outer query.

I need to write inner query something like this which will give me a list of dates between two date ranges (I am trying this query whcih does not execute).

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

My inner query returns following 2 rows:

Select * from innerTable Where ID =100


    Start date           end date 
    3/19/2013            3/21/2013 
    3/8/2013             3/8/2013

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

Does this answer your question? [Generate a range of dates using SQL](https://stackoverflow.com/questions/418318/generate-a-range-of-dates-using-sql) — Vadzim, May 13 '20 at 16:30

score 5 · Accepted Answer · answered Mar 08 '13 at 21:03

Great question - this one really drew me in! The answer was more or less buried further down on Tom's post. Here's the short version, using a table called TestDR to define the ranges. First the TestDR contents:

SELECT * FROM TestDR;

STARTDATE ENDDATE
--------- ---------
19-MAR-13 21-MAR-13
08-MAR-13 08-MAR-13

Now for the query to create one row for each date in the range:

WITH NUMS AS (
  SELECT LEVEL-1 DaysToAdd
  FROM DUAL
  CONNECT BY LEVEL <= 60
)
SELECT StartDate + DaysToAdd TheDate
FROM TestDR
CROSS JOIN NUMS
WHERE TestDR.EndDate - TestDR.StartDate + 1 > DaysToAdd
ORDER BY 1

THEDATE
---------
08-MAR-13
19-MAR-13
20-MAR-13
21-MAR-13

With the query adapted from Tom's posting, you have to know the maximum range going in to "seed" the NUMS query. He used 60 in his example so that's what I used above. If you don't think any row from your subquery will ever have a range of more than 60 days then this will do the trick. If you think the maximum could be as much as 1000 days (about three years) then change the 60 to 1000. I tried this and queried a 2 1/2 year range and the results were instantaneous.

If you want to specify the exact "seed" count you can calculate it if you're willing to make the query a bit more complicated. Here's how I can do it with my TestDR table:

WITH NUMS AS (
  SELECT LEVEL-1 DaysToAdd
  FROM DUAL
  CONNECT BY LEVEL <= (
    SELECT MAX(EndDate - StartDate + 1)
    FROM TestDR)
)
SELECT ... and the rest of the query as above

score 3 · Answer 2 · answered Mar 08 '13 at 21:19

For your problem, you don't need to enumerate the dates. A simple JOIN suffices.

SELECT o.*
  FROM outerTable o
 INNER JOIN innerTable i
    ON i.ID = 100
   AND o.my_date BETWEEN i.REQ_DT and i.DUE_DT

From your code, I can tell that you must be a OO programmer and not familiar with SQL. It does a lot for you, so don't try to control it. It will hinder it's optimization features.

Don't take this in the wrong way, I had the same mindset (believing that I am smarter than the machine).

score 0 · Answer 3 · answered Mar 08 '13 at 19:27

0

In your outer query use an OR statement, which allows your date to be equal to either the return Start_Date or End_Date

AND (date = subQuery.Start_Date 
  OR date = subQuery.End_Date)

answered Mar 08 '13 at 19:27

Matt Busche

14,216
5
36
61

Art · Answer 4 · 2013-03-08T20:19:53.560

Using your dates:

SELECT smth... FROM some_tab
 WHERE your_date IN  
( -- remove unnecessary columns, leave only what you select in outer query 
  -- or select * 
SELECT start_date
 , TRUNC(start_date, 'iw')                  wk_starts  
 , TRUNC(start_date, 'iw') + 7 - 1/86400    wk_ends
 , TO_NUMBER (TO_CHAR (start_date, 'IW'))   ISO_wk#
 FROM 
(
SELECT (start_date-1) + LEVEL AS start_date
 FROM 
( -- replace this part with selecting your start and end dates from your table --
SELECT to_date('03/21/2013', 'MM/DD/YYYY') end_date 
     , to_date('03/19/2013', 'MM/DD/YYYY')  start_date 
 FROM dual
)
CONNECT BY LEVEL <= (end_date - start_date)
)
) -- your outer query ends --
/

START_DATE    WK_STARTS    WK_ENDS                ISO_WK#
----------------------------------------------------------
3/19/2013    3/18/2013    3/24/2013 11:59:59 PM    12
3/20/2013    3/18/2013    3/24/2013 11:59:59 PM    12

Annual table of dates and ISO weeks etc... Use any dates for start and end dates. The connect by and number of days between is used to generate table on the fly. You may use between operator if using hard structures...:

SELECT start_date
 , TRUNC(start_date, 'iw')                  wk_starts  
 , TRUNC(start_date, 'iw') + 7 - 1/86400    wk_ends
 , TO_NUMBER (TO_CHAR (start_date, 'IW'))   ISO_wk#
 FROM 
 (-- This part simplifies above formatting and optional --
 SELECT (start_date-1) + LEVEL AS start_date
   FROM 
  (-- Replace start/end dated with any dates --
  SELECT TRUNC(ADD_MONTHS (SYSDATE, 12), 'Y')-1 end_date  
       , TRUNC(SYSDATE, 'YEAR')                 start_date 
   FROM dual
  )
  CONNECT BY LEVEL <= (end_date - start_date) -- number of days between dates 
 )
 /

START_DATE    WK_STARTS    WK_ENDS                  ISO_WK#
-----------------------------------------------------------
1/1/2013      12/31/2012    1/6/2013 11:59:59 PM      1
1/2/2013      12/31/2012    1/6/2013 11:59:59 PM      1
1/3/2013      12/31/2012    1/6/2013 11:59:59 PM      1
...
12/28/2013    12/23/2013    12/29/2013 11:59:59 PM    52
12/29/2013    12/23/2013    12/29/2013 11:59:59 PM    52
12/30/2013    12/30/2013    1/5/2014 11:59:59 PM      1

Query which will give a list of dates between two date ranges

4 Answers4

Linked