How to populate a table with a range of dates?

Try this:

DROP PROCEDURE IF EXISTS filldates;DELIMITER |CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)BEGIN  WHILE dateStart <= dateEnd DO    INSERT INTO tablename (_date) VALUES (dateStart);    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);  END WHILE;END;|DELIMITER ;CALL filldates('2011-01-01','2011-12-31');

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/65d13/1

EDIT (to check if date already exists) as asked by Andrew Fox.

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)BEGINDECLARE adate date;    WHILE dateStart <= dateEnd DO        SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart);        IF adate IS NULL THEN BEGIN            INSERT INTO MyDates (mydate) VALUES (dateStart);        END; END IF;        SET dateStart = date_add(dateStart, INTERVAL 1 DAY);    END WHILE;END;//

Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/66f86/1

I did not want my SQL query to require external dependencies (needing to have a calendar table, procedure for populating a temporary table with dates, etc.) The original idea for this query came from http://jeffgarretson.wordpress.com/2012/05/04/generating-a-range-of-dates-in-mysql/ which I had slightly optimized for clarity and ease of use.

SELECT (CURDATE() - INTERVAL c.number DAY) AS dateFROM (SELECT singles + tens + hundreds number FROM ( SELECT 0 singlesUNION ALL SELECT   1 UNION ALL SELECT   2 UNION ALL SELECT   3UNION ALL SELECT   4 UNION ALL SELECT   5 UNION ALL SELECT   6UNION ALL SELECT   7 UNION ALL SELECT   8 UNION ALL SELECT   9) singles JOIN (SELECT 0 tensUNION ALL SELECT  10 UNION ALL SELECT  20 UNION ALL SELECT  30UNION ALL SELECT  40 UNION ALL SELECT  50 UNION ALL SELECT  60UNION ALL SELECT  70 UNION ALL SELECT  80 UNION ALL SELECT  90) tens  JOIN (SELECT 0 hundredsUNION ALL SELECT  100 UNION ALL SELECT  200 UNION ALL SELECT  300UNION ALL SELECT  400 UNION ALL SELECT  500 UNION ALL SELECT  600UNION ALL SELECT  700 UNION ALL SELECT  800 UNION ALL SELECT  900) hundredsORDER BY number DESC) c  WHERE c.number BETWEEN 0 and 364

It is simple to optimize and scale this table for other uses. You can easily get rid of the tens and hundreds tables if you only need one week of data.

If you need a larger set of numbers, it is easy to add a thousands table. You only need to copy and paste the table with hundreds and add a zero to 9 numbers.

if you're in a situation like me where procedures are prohibited, and your sql user does not have permissions for insert, therefore insert not allowed, but you want to generate a list of dates in a specific period, say current year to do some aggregation, use this

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) vwhere gen_date between '2017-01-01' and '2017-12-31'