How to generate dates between 2 dates in multiple rows in oracle?

Question

I have a table create table date_period(dt_from date, dt_to date); and I want to generate 1 column output with dates between every 2 dates that I have.

I wrote this:

select (dt_from - 1 + ROWNUM) 
from date_period 
connect by ROWNUM <= (trunc(dt_to) - trunc(dt_from) + 1)

but it doesn't work properly for some reason, it only works when there's only 1 row in the date_period table.

What is my mistake? How do I fix this?

For example, if I have 2 rows in my table:

13-FEB-2023, 15-FEB-2023
20-JUN-2023, 22-JUN-2023

I want to return this:

13-FEB-2023
14-FEB-2023
15-FEB-2023
20-JUN-2023
21-JUN-2023
22-JUN-2023

But with the query I wrote, I get this:

13-FEB-23
14-FEB-23
15-FEB-23
23-JUN-23

I was able to achieve this using recursive common table expressions, but how do I do this with connect by?

I have to agree with @pavel_orekhov "Generate a range of dates using SQL" is a very generic question and so are the answers. Here there is a problem of a query unexpectedly stopping plus a data model constraint that does not exist in the prior question. see: https://stackoverflow.com/questions/418318/generate-a-range-of-dates-using-sql — Paul Maxwell, May 03 '23 at 02:28

Paul Maxwell · Accepted Answer · 2023-05-03T03:17:34.633

You need the use of PRIOR dt_from = dt_from in the query to ensure that the generated dates are treated as rows. Without this the query simply stops at the final actual data row. AND PRIOR SYS_GUID() IS NOT NULL is introduced to avoid creating a Cartesian product.

CREATE TABLE date_period(dt_from DATE, dt_to DATE);

INSERT INTO date_period(dt_from, dt_to) VALUES (DATE '2023-02-13', DATE '2023-02-15');
INSERT INTO date_period(dt_from, dt_to) VALUES (DATE '2023-06-20', DATE '2023-06-22');

/*
Using a hierarchical query with the CONNECT BY clause 
to generate the dates between dt_from and dt_to for each row in the table. 
The PRIOR conditions used to prevent the query from generating a Cartesian product.
*/
SELECT dt_from + LEVEL - 1 AS dates
FROM date_period
CONNECT BY LEVEL <= dt_to - dt_from + 1
AND PRIOR dt_from = dt_from
AND PRIOR SYS_GUID() IS NOT NULL;

DATES
13-FEB-23
14-FEB-23
15-FEB-23
20-JUN-23
21-JUN-23
22-JUN-23

A more "modern" and SQL standard compliant approach would be to adopt a "recursive CTE" instead of Oracle's proprietary syntax. e.g.

/*
This recursive CTE generates the dates between dt_from and dt_to for each row in the table 
by repeatedly adding one day to dt_from until it is equal to dt_to.
*/
WITH date_range (dt_from, dt_to) AS (
  SELECT dt_from, dt_to FROM date_period
  UNION ALL
  SELECT dt_from + 1, dt_to FROM date_range WHERE dt_from < dt_to
)
SELECT dt_from AS dates FROM date_range ORDER BY dt_from;

DATES
13-FEB-23
14-FEB-23
15-FEB-23
20-JUN-23
21-JUN-23
22-JUN-23

fiddle

Cautionary Note

There is nothing I can see to stop your date ranges from overlapping and consequently the output would repeat dates. So to avoid this you may need to adopt SELECT DISTINCT into eiter of the queries above.

fiddle with duplicates

How to generate dates between 2 dates in multiple rows in oracle?

1 Answers1