CODEWITHSUNDEEP
sqloracle-databasedate-arithmetic
Abhishek
Abhishek

Reputation: 680

List of dates between two tables in oracle

Problem

Input:

START_DATE      END_DATE 

01-FEB-16       03-FEB-16   
01-FEB-16       02-FEB-16 
10-FEB-16       11-FEB-16

I want to generate all the dates between the start_day and end_day as

Output

01-FEB-16    
02-FEB-16     
03-FEB-16     
10-FEB-16     
11-FEB-16

Upvotes: 1

Views: 107

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

You could do it using Row Generator technique.

Setup

SQL> CREATE TABLE t
  2    (START_DATE DATE, END_DATE DATE
  3    );

Table created.

SQL> INSERT INTO t VALUES(DATE '2016-02-01', DATE '2016-02-03');

1 row created.

SQL> INSERT INTO t VALUES(DATE '2016-02-01', DATE '2016-02-02');

1 row created.

SQL> INSERT INTO t VALUES(DATE '2016-02-10', DATE '2016-02-11');

1 row created.

SQL> COMMIT;

Commit complete.

Query

SQL> SELECT DISTINCT TO_CHAR(START_DATE+LEVEL-1, 'DD-MON-YYYY') the_date
  2  FROM t
  3    CONNECT BY LEVEL <= END_DATE-START_DATE+1
  4  ORDER BY the_date
  5  /

THE_DATE
-----------
01-FEB-2016
02-FEB-2016
03-FEB-2016
10-FEB-2016
11-FEB-2016

SQL>

Upvotes: 3

Related Questions