Generating dates between two dates

Question

I need to generate all dates between two given dates. This works fine as long as there is just one date range. However, if I have multiple date ranges, this solution doesn't work. I have searched here as well as on asktom, but couldn't locate relevant pointers/solution.

I tried both the solutions using all_objects and CONNECT BY ROWNUM, but no luck. Here is the problem statement: sqlfiddle

Input

ID  START_DATE      END_DATE 
101 April, 01 2013  April, 10 2013 
102   May, 10 2013    May, 12 2013

Output

ID  Dates
101 April, 01 2013 
101 April, 02 2013  
101 April, 03 2013  
101 April, 04 2013  
101 April, 05 2013  
101 April, 06 2013  
101 April, 07 2013  
101 April, 08 2013  
101 April, 09 2013  
101 April, 10 2013  
102   May, 10 2013  
102   May, 11 2013  
102   May, 12 2013

One more thing to add here is that _t_dates_ table is not that simple table instead a complex one bringing lot of information apart from just _ID_, _START_DATE_, _END_DATE_. — Vishal, Apr 25 '13 at 08:58
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:20

score 8 · Accepted Answer · answered Apr 25 '13 at 06:26

select 
  A.ID, 
  A.START_DATE+delta dt
from 
  t_dates A, 
  (
     select level-1 as delta 
     from dual 
     connect by level-1 <= (
       select max(end_date - start_date) from t_dates
     )
  )
where A.START_DATE+delta <= A.end_date
order by 1, 2

score 4 · Answer 2 · answered Apr 25 '13 at 06:28

4

Please try:

select 
  distinct ID, 
  START_DATE+level-1 DATES
from dual a, TABLE_DATES b
connect by level <= (END_DATE-START_DATE)+1
order by ID;

answered Apr 25 '13 at 06:28

TechDo

18,398
3
51
64

Tried this solution, but this didn't work. I replaced the t_dates table with a complex table, and the resultset is not proper. Anyway thanks for the help.. :) – Vishal Apr 25 '13 at 09:19

score 1 · Answer 3 · answered May 21 '15 at 16:56

    select g.cycle_dt
        from 
            (select to_date(d,'DD-MM-YYYY') cycle_dt
               from dual
                    model
                    dimension by (trunc(to_date('30092015', 'DDMMYYYY')) d) 
                    measures (0 y)
                    rules (
                    y[for d from trunc(to_date('30092015', 'DDMMYYYY')) to to_date('30102015', 'DDMMYYYY')  increment 1]=0
                    )) g
        order by g.cycle_dt;

score 0 · Answer 4 · edited Nov 22 '19 at 06:58

0

WITH NUMS AS (
  SELECT LEVEL-1 DaysToAdd
  FROM DUAL
  CONNECT BY LEVEL <= (
    SELECT MAX(END_DATE - START_DATE + 1)
    FROM HOLIDAY)
)
SELECT START_DATE + DaysToAdd DATE_TIME, DESCRIPTION, IS_SCHOOL_HOLIDAY, UPDATE_BY, CREATE_DATE
FROM HOLIDAY
CROSS JOIN NUMS
WHERE HOLIDAY.END_DATE - HOLIDAY.START_DATE  > DaysToAdd
ORDER BY 1, 2;

edited Nov 22 '19 at 06:58

4b0

21,981
30
95
142

answered Nov 22 '19 at 06:53

Rajesh Raja

1

score 0 · Answer 5 · edited Apr 26 '22 at 06:20

0

SELECT TO_DATE('1-JAN-21', 'dd-mm-yy') + ROWNUM - 1
  FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('26-APR-22', 'dd-mm-yy') -
           TO_DATE('1-JAN-21', 'dd-mm-yy')) + 1;

edited Apr 26 '22 at 06:20

Suraj Rao

29,388
11
94
103

answered Apr 26 '22 at 06:02

Usman Rajput

1

Generating dates between two dates

5 Answers5

Linked

Related