How to get dates between two date columns?

Question

I have two date columns, START_DATE and END_DATE and need to get a list of dates between two date columns in Oracle.

START_DATE   | END_DATE
04-JUN-19    | 06-JUN-19
11-AUG-19    | 13-AUG-19

Found a similar problem for sql server but couldn't convert to oracle pl/sql:

Need to get dates between two date columns

Tried like this but doesn't show the desired result.

with dates (dte, edte) as (
      select A.START_DATE, A.END_DATE
      FROM tbl A
      WHERE A.START_DATE <> A.END_DATE
      union all
      select dte + 1, edte
      from dates
      where dte < edte
     )
select dte
from dates;

I want to get list dates like:

But showing:

04-JUN-19
11-AUG-19

score 1 · Answer 1 · answered Jun 17 '19 at 07:53

Here's how:

SQL> with test (stadat, enddat) as
  2    (select date '2019-06-04', date '2019-06-06' from dual union all
  3     select date '2019-08-11', date '2019-08-13' from dual
  4    )
  5  select t.stadat + column_value - 1 datum
  6  from test t join table(cast(multiset(select level from dual
  7                                       connect by level <= t.enddat - t.stadat + 1
  8                                      ) as sys.odcinumberlist )) on 1 = 1
  9  order by datum;

DATUM
----------
2019-06-04
2019-06-05
2019-06-06
2019-08-11
2019-08-12
2019-08-13

6 rows selected.

SQL>

score 1 · Answer 2 · answered Jun 17 '19 at 07:56

One way of generating consecutive integers is to use CONNECT BY:

select level n from dual
connect by level <= 3;

N
-
1
2
3

Using Oracle "date arithmetic" (where adding 1 means adding one day), you can say:

select date '2019-06-01' + level - 1 dte from dual
connect by level <= 3;

DTE             
----------------
2019-06-01 00:00
2019-06-02 00:00
2019-06-03 00:00

Finally, if you have version 12c or later, you can use the LATERAL clause to apply this logic to each row. Note that the WITH subquery is not a part of the solution, it is there to generate the input data.

with data(START_DATE, END_DATE) as (
  select date '2019-06-04', date '2019-06-06' from dual
  union all
  select date '2019-08-11', date '2019-08-13' from dual
)
select * from data, lateral(
  select start_date + level - 1 dte
  from dual
  connect by start_date + level - 1 <= end_date
);

START_DATE       END_DATE         DTE             
---------------- ---------------- ----------------
2019-06-04 00:00 2019-06-06 00:00 2019-06-04 00:00
2019-06-04 00:00 2019-06-06 00:00 2019-06-05 00:00
2019-06-04 00:00 2019-06-06 00:00 2019-06-06 00:00
2019-08-11 00:00 2019-08-13 00:00 2019-08-11 00:00
2019-08-11 00:00 2019-08-13 00:00 2019-08-12 00:00
2019-08-11 00:00 2019-08-13 00:00 2019-08-13 00:00

Best regards, Stew Ashton

P.S. If you have an older version, the first answer (which I just saw) uses the same logic except that instead of LATERAL it uses TABLE(CAST(MULTISET...

score 1 · Answer 3 · answered Jun 17 '19 at 08:03

In your question, you tried recursive subquery factoring which is a SQL standard. To use that technique I believe you need version 11.2 at least.

with data(START_DATE, END_DATE) as (
  select date '2019-06-04', date '2019-06-06' from dual
  union all
  select date '2019-08-11', date '2019-08-13' from dual
)
, recurse_dates(start_date, end_date, dte) as (
  select start_date, end_date, start_date from data
  union all
  select start_date, end_date, dte + 1
  from recurse_dates
  where dte < end_date
)
select * from recurse_dates;

START_DATE       END_DATE         DTE             
---------------- ---------------- ----------------
2019-06-04 00:00 2019-06-06 00:00 2019-06-04 00:00
2019-08-11 00:00 2019-08-13 00:00 2019-08-11 00:00
2019-06-04 00:00 2019-06-06 00:00 2019-06-05 00:00
2019-08-11 00:00 2019-08-13 00:00 2019-08-12 00:00
2019-06-04 00:00 2019-06-06 00:00 2019-06-06 00:00
2019-08-11 00:00 2019-08-13 00:00 2019-08-13 00:00

Regards, Stew

This does not work in Oracle 11gR2 ([db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=fc4d871b15ddd8f5e587211dfe354f8e)) as there is a bug in how it handles dates in recursive sub-query factoring clauses. It will work in Oracle 18c ([db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=f4d81d795eaba5904e65dbca5f81d5d8)). — MT0, Jun 17 '19 at 09:18
More specifically, bug 11840579 was fixed in Oracle 11.2.0.3 and 12.1.0.1. The query will not work in versions before this. — MT0, Jun 17 '19 at 09:32

Barbaros Özhan · Answer 4 · 2019-06-17T10:31:02.640

0

You can get the result easily by using connect by level clause in Oracle :

with t( start_date, end_date ) as
(
 select date'2019-06-04', date'2019-06-06' from dual union all
 select date'2019-06-11', date'2019-06-13' from dual
)
select distinct start_date + level - 1 as "Dates" 
  from t
 connect by level <= end_date - start_date + 1 
    and prior start_date = start_date
    and prior sys_guid() is not null;

Dates
----------
04.06.2019
05.06.2019
06.06.2019
11.06.2019
12.06.2019
13.06.2019

Demo

distinct clause was added against overlapping dates between ranges.

edited Jun 17 '19 at 10:31

answered Jun 17 '19 at 07:56

Barbaros Özhan

59,113
10
31
55

This will create an exponential number of duplicates if you have multiple ranges with the same start date [db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=68236dec21a2785e5fdf34cbc335dc7c). – MT0 Jun 17 '19 at 08:56
@MT0 well, against overlapping ranges cases, just added a `distinct` clause. – Barbaros Özhan Jun 17 '19 at 10:32
But if there are two overlapping ranges then you may want the duplicates as there should be one row for each date in each range. What you do not want is the excess duplication that this solution generates as it will cross-correlate the rows from each range and as the size of the duplicate range increases then an exponential number of cross-correlations are made. – MT0 Jun 17 '19 at 10:36

MT0 · Answer 5 · 2019-06-17T09:27:10.633

Your code works on Oracle 18c:

with dates (dte, edte) as (
      select A.START_DATE, A.END_DATE
      FROM tbl A
      WHERE A.START_DATE <> A.END_DATE
      union all
      select dte + 1, edte
      from dates
      where dte < edte
     )
select dte
from dates;

Outputs:

| DTE       |
| :-------- |
| 04-JUN-19 |
| 11-JUN-19 |
| 05-JUN-19 |
| 12-JUN-19 |
| 06-JUN-19 |
| 13-JUN-19 |

Oracle 18c db<>fiddle here

However, there is a bug (number 11840579) in Oracle 11 (that was fixed in 11.2.0.3 and 12.1.0.1) relating to dates in recursive sub-queries and the easiest solution for a non-fixed version is not to add to the date but to have an extra column with a non-date column to handle the increment:

WITH dates (dte, edte, step ) AS (
  SELECT A.START_DATE, A.END_DATE, 0
  FROM   tbl A
UNION ALL
  SELECT dte, edte, step + 1
  FROM   dates
  WHERE  dte + step + 1 <= edte
)
SELECT dte + step
FROM   dates;

Outputs:

| DTE+STEP  |
| :-------- |
| 04-JUN-19 |
| 11-JUN-19 |
| 05-JUN-19 |
| 12-JUN-19 |
| 06-JUN-19 |
| 13-JUN-19 |

Oracle 11gR2 db<>fiddle here

How to get dates between two date columns?

5 Answers5