How do I generate days from the range of dates in MySQL?

It can be done with the help of following query which uses adddate() function and we are generating the days between ‘2016-12-15’ and ‘2016-12-31’ −

mysql> select * from
    -> (select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) gen_date from
    -> (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    -> (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    -> (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    -> (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    -> (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
    -> Where gen_date between '2016-12-15' and '2016-12-31'
    -> ;
+------------+
| gen_date   |
+------------+
| 2016-12-15 |
| 2016-12-16 |
| 2016-12-17 |
| 2016-12-18 |
| 2016-12-19 |
| 2016-12-20 |
| 2016-12-21 |
| 2016-12-22 |
| 2016-12-23 |
| 2016-12-24 |
| 2016-12-25 |
| 2016-12-26 |
| 2016-12-27 |
| 2016-12-28 |
| 2016-12-29 |
| 2016-12-30 |
| 2016-12-31 |
+------------+
17 rows in set (0.30 sec)

Nikitha N

Published on 19-Feb-2018 09:50:53

• Related Questions & Answers
• How can I generate days from the range of dates with the help of MySQL views?
• How do I get the number of days between two dates in JavaScript?
• How do I calculate number of days between two dates using Python?
• Python Pandas - Generate dates in a range
• How can I calculate full 24hour days between two specified dates in MySQL?
• Display dates after NOW() + 10 days from a MySQL table?
• How can I count the number of days in MySQL?
• MySQL select dates in 30-day range?
• How do I generate random floats in C++?
• MySQL query to calculate the days between two dates from different columns but similar rows
• How do I set the timezone of MySQL?
• How to get the number of days between two Dates in JavaScript?
• How do I change the range of the X-axis with datetimes in Matplotlib?
• Finding Number of Days Between Two Dates JavaScript
• How can I generate random number in a given range in Android?