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:
How should I do the query? Thanks in advance.
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).
Alternate solution
Creating table -
create table mytable(start_date date, end_date date);
Loading data -
insert into mytable values (to_date('01/31/2020','mm/dd/yyyy'),to_date('02/03/2020','mm/dd/yyyy'));
SQL to retrieve list of dates between the given date range -
select
t.start_date + rownum -1 as dt,
t.start_date,
t.end_date
from
all_objects, mytable t
where
rownum <=
t.end_date- t.start_date+1;
db fiddle - https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=7cd81f5892ad195b72ee1d22a3b44e2a
For actual Oracle versions:
SELECT *
FROM t1,
lateral(
select level N,
t1.startdate+level-1 dt
from dual
connect by level <= t1.enddate - t1.startdate + 1
);
or
SELECT
startdate,
enddate,
startdate+n as DT
FROM t1,
xmltable('0 to xs:integer(.)'
passing cast(enddate-startdate as number)
columns n int path '.'
)
Full examples:
with 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,
lateral(
select level N,
t1.startdate+level-1 dt
from dual
connect by level <= t1.enddate - t1.startdate + 1
);
with 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
startdate,
enddate,
startdate+n as DT
FROM t1,
xmltable('0 to xs:integer(.)'
passing cast(enddate-startdate as number)
columns n int path '.'
);
To just generate a list of dates between dates this is the simplest way:
This will generate 30 Date records from the 1st of October 2024 to the 30th of October 2024 as an example.
SELECT TRUNC(DATE '2024-10-01')-1 + LEVEL, LEVEL
FROM DUAL CONNECT BY LEVEL < ABS(DATE '2024-10-01' - DATE '2024-11-01') -- Specify Date range here this will just result in a int for number of days
ORDER BY LEVEL
If you need 1st of October to 1st of November (inclusive) just make the Connect by condition <= instead of <
Or if you want to generate a lets say 10 date records from a starting date:
SELECT TRUNC(DATE '2024-10-01')-1 + LEVEL, LEVEL
FROM DUAL CONNECT BY LEVEL <= 10
ORDER LEVEL
Easy, no special oracle version needed.
