Find dates between start date and end date in mysql

Question

I got 2 columns, one is start date and the other is end date. I need to find every single day between the two dates.

Need it in another table with two coloums, one name pno that refers to the pno's id in the first table, and the other with the dates between start date and end date.

As an example this could be my input

 
pno         startdate        end date
p1          2012-12-03      2012-12-06     
p2          2013-01-05      2013-01-08
p3          2013-01-15      2012-01-20

and this has to be my output.

pno          dates
----------
p01          2012-12-03 
p01          2012-12-04 
p01          2012-12-05 
p01          2012-12-06
p02          2013-01-05
p02          2013-01-06
p02          2013-01-07
p02          2013-01-08
p03          2013-01-15
...

peterm peterm · Accepted Answer · 2013-09-28T07:37:41

You can do it like this

SELECT pno, startdate + INTERVAL q.n - 1 DAY dates
  FROM table1 t CROSS JOIN
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) q 
 WHERE q.n - 1 <= DATEDIFF(enddate, startdate) 
 ORDER BY pno, dates

The subquery generates a sequence of numbers from 1 to 100. You can adjust it for your needs (if date differences span more or less than 100 days) or completely substitute it with a persisted tally(numbers) table if you do a lot of such queries.

Output:

+------+------------+
| pno  | dates      |
+------+------------+
| p1   | 2012-12-03 |
| p1   | 2012-12-04 |
| p1   | 2012-12-05 |
| p1   | 2012-12-06 |
| p2   | 2013-01-05 |
| p2   | 2013-01-06 |
| p2   | 2013-01-07 |
| p2   | 2013-01-08 |
| p3   | 2013-01-15 |
| p3   | 2013-01-16 |
| p3   | 2013-01-17 |
| p3   | 2013-01-18 |
| p3   | 2013-01-19 |
| p3   | 2013-01-20 |
+------+------------+

Here is SQLFiddle demo

UPDATE: To create and populate persisted tally table use

CREATE TABLE tally (n INT NOT NULL PRIMARY KEY);

INSERT INTO tally (n)
SELECT a.n + b.n * 10 + c.n * 100 + d.n * 1000 + 1 n
 FROM 
(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
,(SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d
ORDER BY n;

You'll have in the tally table sequence of numbers from 1 to 10000. That will allow you to work with date ranges that span more than 27 years.

Now the query boils down to

SELECT pno, startdate + INTERVAL q.n - 1 DAY dates
  FROM table1 t CROSS JOIN tally q 
 WHERE q.n - 1 <= DATEDIFF(enddate, startdate) 
 ORDER BY pno, dates

Here is SQLFiddle demo

Find dates between start date and end date in mysql

1 Answers