List of dates between two tables in oracle

Question

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

Check http://stackoverflow.com/a/16207828/68868 for an answer to a similar question. Just add a distinct to the outermost select and you'll get unique dates. — Razvan Stefanescu, Feb 11 '16 at 08:27
See [**Row Generator**](https://lalitkumarb.com/2015/04/15/generate-date-month-name-week-number-day-number-between-two-dates-in-oracle-sql/) — Lalit Kumar B, Feb 11 '16 at 08:38
Possible duplicate of [generating dates between two columns](http://stackoverflow.com/questions/35332130/generating-dates-between-two-columns) — Noel, Feb 11 '16 at 08:59

score 3 · Accepted Answer · answered Feb 11 '16 at 08:37

3

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>

answered Feb 11 '16 at 08:37

Lalit Kumar B

47,486
13
97
124

1

@Abhishek What error? I have posted a working example. Copy paste your SQL*Plus session and show what exactly you are doing, just like I have shown you. – Lalit Kumar B Feb 11 '16 at 09:15
sorry buddy. It is working fine. Last time missed something while executing . Worked really hard for 2 days but didn't get the result. Finally I got. Thanks a lot – Abhishek Feb 11 '16 at 09:22
@Abhishek You're welcome. Please mark it as answered, would help others too! – Lalit Kumar B Feb 11 '16 at 09:28
One more thing. Suppose I want to check with one clause like "Where". How to do? – Abhishek Feb 11 '16 at 10:33
Instead of `from t` use a subquery. `from (select blah blah blah from t where...) connect by` – Lalit Kumar B Feb 11 '16 at 10:34
Please mark it as answered, there is a tick mark below the votes left side. – Lalit Kumar B Feb 11 '16 at 10:34
Working correctly if I am writing (Select * blah..blah.. from t where....). Suppose I want to print a column name . How to do. Say I want to print a column called Name ,say something like this. SELECT Name,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 above query is giving error. – Abhishek Feb 11 '16 at 11:12
`SELECT DISTINCT name, TO_CHAR(START_DATE+LEVEL-1, 'DD-MON-YYYY') the_date FROM (select * from t where name = 'abc') CONNECT BY LEVEL <= END_DATE-START_DATE+1 ORDER BY the_date /` – Lalit Kumar B Feb 11 '16 at 11:13

List of dates between two tables in oracle

1 Answers1