I need to do a while between a start date and an end date, that is, as long as the start date is less than the end date, it will return a query, how can I increment this while to make it run?
I need to do a while between a start date and an end date, that is, as long as the start date is less than the end date, it will return a query, how can I increment this while to make it run?
My understanding is that there is no standard way of doing this; you need to use some proprietary mechanism of your DBMS, and MySQL is especially poor in that regard. You can do a stored procedure :
DELIMITER GO
CREATE TABLE 'Calendar' (
'Date' DATE NOT NULL PRIMARY KEY)
GO
CREATE PROCEDURE 'PopulateCalendar'('startDate' DATE, 'endDate' DATE)
BEGIN
WHILE 'startDate' < 'endDate' DO
INSERT 'Calendar' VALUES ('startDate');
SET 'startDate' = DATE_ADD('startDate', INTERVAL 1 DAY);
END WHILE;
END
GO
CALL 'PopulateCalendar'('2015-01-01', '2024-12-31');
GO
SELECT * FROM 'Calendar' WHERE 'Date' BETWEEN '2015-04-01' AND '2015-04-30'
GO
You may want to put appropriate indexes in Calendar
and / or edit PopulateCalendar
to avoid trouble when you have create dates that already exist.
( SQLFiddle )
The answer lies in this topic: link
I only found the question after asking.