generate days from date range

This solution uses no loops, procedures, or temp tables. The subquery generates dates for the last 10,000 days, and could be extended to go as far back or forward as you wish.

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) awhere a.Date between '2010-01-20' and '2010-01-24' 

Output:

Date----------2010-01-242010-01-232010-01-222010-01-212010-01-20

Notes on Performance

Testing it out here, the performance is surprisingly good: the above query takes 0.0009 sec.

If we extend the subquery to generate approx. 100,000 numbers (and thus about 274 years worth of dates), it runs in 0.0458 sec.

Incidentally, this is a very portable technique that works with most databases with minor adjustments.

SQL Fiddle example returning 1,000 days

Here is another variation using views:

CREATE VIEW digits AS  SELECT 0 AS digit 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;CREATE VIEW numbers AS  SELECT    ones.digit + tens.digit * 10 + hundreds.digit * 100 + thousands.digit * 1000 AS number  FROM    digits as ones,    digits as tens,    digits as hundreds,    digits as thousands;CREATE VIEW dates AS  SELECT    SUBDATE(CURRENT_DATE(), number) AS date  FROM    numbers;

And then you can simply do (see how elegant it is?):

SELECT  dateFROM  datesWHERE  date BETWEEN '2010-01-20' AND '2010-01-24'ORDER BY  date

Update

It is worth noting that you will only be able to generate past dates starting from the current date. If you want to generate any kind of dates range (past, future, and in between), you will have to use this view instead:

CREATE VIEW dates AS  SELECT    SUBDATE(CURRENT_DATE(), number) AS date  FROM    numbers  UNION ALL  SELECT    ADDDATE(CURRENT_DATE(), number + 1) AS date  FROM    numbers;

Accepted answer didn't work for PostgreSQL (syntax error at or near "a").

The way you do this in PostgreSQL is by using generate_series function, i.e.:

SELECT day::dateFROM generate_series('2010-01-20', '2010-01-24', INTERVAL '1 day') day;    day------------ 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24(5 rows)