Convert table with date range into list of dates

Question

I have a table called dateRange which looks like this

|ID|dateStart|  dateEnd|
| 1|14-May-14|16-May-14|
| 1|20-May-14|21-May-14|
| 2|20-May-14|21-May-14|

Which I would like to convert to the following format in a table called dateListed

|ID|     date|
| 1|14-May-14|
| 1|15-May-14|
| 1|16-May-14|
| 1|20-May-14|
| 1|21-May-14|
| 2|20-May-14|
| 2|21-May-14|

I have reviewed the following question, Convert Date Range to Individual Days, however, as the data is slightly different I was hoping someone could assist me?

Thanks.

@Strawberry are you referring to the dateRange table? If so, it's just a list of dates that I have in excel that I have imported into a table. — Hammer, Jun 08 '14 at 22:19
Duplicate of http://stackoverflow.com/questions/15223032/mysql-display-all-date-in-between-range and http://stackoverflow.com/questions/2157282/generate-days-from-date-range found as top 2 results of Google search terms "mysql list of dates from date range". — Sybeus, Jun 08 '14 at 23:05

score 1 · Accepted Answer · answered Jun 08 '14 at 22:21

If you have a utility table of dates, then it's easy...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(ID INT NOT NULL 
,dateStart date NOT NULL
,dateEnd date NOT NULL
,PRIMARY KEY(ID,dateStart)
);

INSERT INTO my_table VALUES
(1,'2014-05-14','2014-05-16'),
(1,'2014-05-20','2014-05-21'),
(2,'2014-05-20','2014-05-21');

SELECT * FROM my_table;
+----+------------+------------+
| ID | dateStart  | dateEnd    |
+----+------------+------------+
|  1 | 2014-05-14 | 2014-05-16 |
|  1 | 2014-05-20 | 2014-05-21 |
|  2 | 2014-05-20 | 2014-05-21 |
+----+------------+------------+

SELECT x.ID
     , c.dt date 
  FROM calendar c 
  JOIN my_table x 
    ON c.dt BETWEEN x.dateStart AND x.dateEnd 
 ORDER  
    BY ID
     , date;
+----+------------+
| ID | date       |
+----+------------+
|  1 | 2014-05-14 |
|  1 | 2014-05-15 |
|  1 | 2014-05-16 |
|  1 | 2014-05-20 |
|  1 | 2014-05-21 |
|  2 | 2014-05-20 |
|  2 | 2014-05-21 |
+----+------------+

Convert table with date range into list of dates

1 Answers1