Search code examples
sqloracle-database

Oracle SQL. Get Dates between two dates

I have a table with two date columns, how can get the dates between these two dates and list them one by one. Here is the test script:

CREATE TABLE t1
   AS
      SELECT DATE '2020-1-31' AS startdate,
             DATE '2020-2-3' AS enddate
      FROM dual
      UNION
      SELECT DATE '2020-2-27' AS startdate,
             DATE '2020-3-3' AS enddate
      FROM dual;

SELECT *
FROM t1;


DROP TABLE t1;

The result set I am expecting is:

enter image description here

How should I do the query? Thanks in advance.

Solution

  • Here you go:

    SQL> select * From t1;

STARTDATE  ENDDATE
---------- ----------
01/31/2020 02/03/2020
02/27/2020 03/03/2020

SQL> select a.startdate, a.enddate,
  2    a.startdate + column_value - 1 dt
  3  from t1 a cross join
  4    table(cast(multiset(select level from dual
  5                        connect by level <= a.enddate - a.startdate + 1
  6                       ) as sys.odcinumberlist))
  7  order by dt;

STARTDATE  ENDDATE    DT
---------- ---------- ----------
01/31/2020 02/03/2020 01/31/2020
01/31/2020 02/03/2020 02/01/2020
01/31/2020 02/03/2020 02/02/2020
01/31/2020 02/03/2020 02/03/2020
02/27/2020 03/03/2020 02/27/2020
02/27/2020 03/03/2020 02/28/2020
02/27/2020 03/03/2020 02/29/2020
02/27/2020 03/03/2020 03/01/2020
02/27/2020 03/03/2020 03/02/2020
02/27/2020 03/03/2020 03/03/2020

10 rows selected.

SQL>

    This is called a row generator technique (if you'd ever want too Google for it).