I have a date column and I need to return each day from the start date to the end date. For example if I have a date column that starts at 01-01-2020 till 22/03/2022. Then I want to return data such as:

CodePudding user response：

From Oracle 12:

SELECT t.id, d.day
FROM   table_name t
       CROSS JOIN LATERAL (
         SELECT t.start_date   LEVEL - 1 AS day
         FROM   DUAL
         CONNECT BY t.start_date   LEVEL - 1 <= t.end_date
       ) d
WHERE  t.start_date <= t.end_date

Before Oracle 12, you can use:

SELECT t.id, d.COLUMN_VALUE AS day
FROM   table_name t
       CROSS JOIN TABLE(
         CAST(
           MULTISET(
             SELECT t.start_date   LEVEL - 1
             FROM   DUAL
             CONNECT BY t.start_date   LEVEL - 1 <= t.end_date
           )
           AS SYS.ODCIDATELIST
         )
       ) d
WHERE  t.start_date <= t.end_date

Or:

WITH dates (id, day, end_date) AS (
  SELECT id, start_date, end_date
  FROM   table_name
  WHERE  start_date <= end_date
UNION ALL
  SELECT id, day   1, end_date
  FROM   dates
  WHERE  day   1 <= end_date
)
SEARCH DEPTH FIRST BY id, day SET ord
SELECT id, day
FROM   dates;

Which, for the sample data:

CREATE TABLE table_name (id, start_date, end_date) AS
SELECT 1, DATE '2021-01-01', DATE '2022-03-22' FROM DUAL UNION ALL
SELECT 2, DATE '2022-01-01', DATE '2022-01-10' FROM DUAL;

All output:

ID	DAY
1	01-JAN-21
1	02-JAN-21
...	...
1	21-MAR-22
1	22-MAR-22
2	01-JAN-22
2	02-JAN-22
...	...
2	09-JAN-22
2	10-JAN-22

db<>fiddle here

CodePudding user response：

One option might also be

SQL> WITH
  2     test (id, start_date, end_date)
  3     AS
  4        (SELECT 1, DATE '2020-01-01', DATE '2022-03-22' FROM DUAL)
  5      SELECT start_date   LEVEL - 1 datecolumn
  6        FROM test
  7  CONNECT BY LEVEL <= end_date - start_date   1
  8    ORDER BY datecolumn;

DATECOLUMN
----------
01.01.2020
02.01.2020
03.01.2020
04.01.2020
<snip>
14.03.2022
15.03.2022
16.03.2022
17.03.2022
18.03.2022
19.03.2022
20.03.2022
21.03.2022
22.03.2022

812 rows selected.

SQL>

If there - as MT0 commented - is more than a single row in that table that contains start/end dates, then

WITH
   test (id, start_date, end_date)
   AS
      (SELECT 1, DATE '2020-01-01', DATE '2022-03-22' FROM DUAL
       UNION ALL
       SELECT 2, DATE '2022-03-15', DATE '2022-03-23' FROM DUAL)
  SELECT id, start_date   COLUMN_VALUE - 1 datecolumn
    FROM test
         CROSS JOIN
         TABLE (
            CAST (
               MULTISET (    SELECT LEVEL
                               FROM DUAL
                         CONNECT BY LEVEL <= end_date - start_date   1)
                  AS SYS.odcinumberlist))
ORDER BY id, datecolumn;