How to populate a table with a range of dates?

by

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)

BEGIN

DECLARE 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

More Related Contents:

  1. Left Outer Join doesn’t return all rows from my left table?
  2. Get a list of dates between two dates
  3. MySQL Query GROUP BY day / month / year
  4. Error in MySQL when setting default value for DATE or DATETIME
  5. Difference between two dates in MySQL
  6. Get the latest date from grouped MySQL data
  7. Checking a table for time overlap?
  8. How to select the most recent set of dated records from a mysql table
  9. Compare only day and month with date field in mysql
  10. Insert/ Update random date in MySQL
  11. #1292 – Incorrect date value: ‘0000-00-00’ [duplicate]
  12. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  13. How do I extract Month and Year in a MySQL date and compare them?
  14. get first occurrence of last changed value of a column [closed]
  15. multiple query same table but in different columns mysql
  16. How can I make SQL case sensitive string comparison on MySQL?
  17. WHERE vs HAVING
  18. Return default result for IN value regardless
  19. How can I search (case-insensitive) in a column using LIKE wildcard?
  20. Search for “whole word match” in MySQL
  21. SQL: selecting rows where column value changed from previous row
  22. Why do results from a SQL query not come back in the order I expect?
  23. How to generate data in MySQL?
  24. MySQL combine two columns and add into a new column
  25. How to skip certain database tables with mysqldump?
  26. MySQL error 1241: Operand should contain 1 column(s)
  27. How to set a MySQL row to READ-ONLY?
  28. Mysql: Order by like?
  29. Getting first day of the week in MySql using Week No
  30. Using DISTINCT and COUNT together in a MySQL Query

Leave a Comment