Stored Routine: Generate a list of dates

MySQL Tips and Tricks

Stored Routine: Generate a list of dates

Usage: CALL dates('2011-09-06', NOW());

CREATE PROCEDURE `dates`(IN `start` DATETIME, IN `end` DATETIME)
    MODIFIES SQL DATA
    DETERMINISTIC
    COMMENT 'Builds a result set of dates, inclusive'
BEGIN

DECLARE d DATE;
DECLARE e DATE;

SET d = DATE(start);
SET e = DATE(end);

DROP TEMPORARY TABLE IF EXISTS `dates`;
CREATE TEMPORARY TABLE IF NOT EXISTS `dates` (`date` DATE NOT NULL);

l: LOOP
    INSERT INTO dates VALUES(d);
    SET d = d + INTERVAL 1 DAY;
    IF d > e THEN
        LEAVE l;
    END IF;
END LOOP l;

END