mrkeck Sharing the Love

4May/170

MySQL Get Consecutive Dates for Date Range

Source - http://stackoverflow.com/questions/2157282/generate-days-from-date-range

Not exactly what I was looking for, but I found the technique very intriguing


select a.dt
from (
select '2018-01-01' - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as dt
from (select 0 as a 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) as a
cross join (select 0 as a 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) as b
cross join (select 0 as a 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) as c
) a
where a.dt between '2017-01-01' and '2017-05-21'
ORDER BY a.dt

Results:
2017-01-01
2017-01-02
2017-01-03
2017-01-04...
2017-05-20
2017-05-21

Filed under: mySQL No Comments