Temporary Date Range Table MySQL

Question

first time poster long time reader. I was wondering if anyone know how to create a Temporary Table that generates a list of dates. Basically what I need this table for is so i can add 1 to each date which is between two other dates from a record. I keep getting errors in mySQL so i was hoping someone could correct me if you see a mistake.

set @start_date = ‘2014-07-29’;
set @end_date = ‘2016-07-29’;

CREATE TEMPORARY TABLE Dates (
d DATE,
PRIMARY KEY (d))

WHILE (@start_date < @end_date)
BEGIN 
INSERT INTO Dates (d) VALUES (@start_date)
SELECT @start_date = DATEADD(DAY, 1, @start_date)

score 1 · Answer 1 · answered Jun 29 '21 at 22:15

SQL has no loops outside of procedures or function, and the syntax is no Mysql syntax

CREATE ProCEDURE mysatetable()
BEGIN
set @start_date = '2014-07-29';
set @end_date = '2016-07-29';

CREATE TEMPORARY TABLE Dates (
d DATE,
PRIMARY KEY (d));

WHILE (@start_date < @end_date) DO
INSERT INTO Dates (d) VALUES (@start_date);
SET @start_date :=  DATE_ADD(@start_date,INTERVAL 1 day);
END WHILE;
END

CALL mysatetable()

SELECT * FROM Dates

| d          |
| :--------- |
| 2014-07-29 |
| 2014-07-30 |
...
| 2016-07-27 |
| 2016-07-28 |

db<>fiddle here

Temporary Date Range Table MySQL

1 Answers1