generate days from date range

Question

I would like to run a query like

select ... as days where `date` is between '2010-01-20' and '2010-01-24'

And return data like:

days

----------

2010-01-20

2010-01-21

2010-01-22

2010-01-23

2010-01-24

Answer 1

The given solution uses no loops, procedures, or temp tables. And, the subquery generates dates for the last 10,000 days, and can be extended to go as far back or forward as you want:

select a.Date 

from (

    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Date

    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

    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 d

) a

where a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date

----------

2010-01-24

2010-01-23

2010-01-22

2010-01-21

2010-01-20

Notes on Performance: Testing it out here, according to the above query it takes 0.0009 sec. But, If you want to extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it'll run in 0.0458 sec.