I am a novice in SQL and I was trying to generate dates within 2 dates that are prespecified by me. I was using the code below, but I get an error in MySQL workbench stating that 'DECLARE is not valid at this position'... What am I doing wrong?
DELIMITER $$
CREATE PROCEDURE proc()
BEGIN
DECLARE @dates TABLE(dt datetime)
DECLARE @dateFrom datetime
DECLARE @dateTo datetime
SET @dateFrom = '2001/01/01'
SET @dateTo = '2001/01/12'
WHILE(@dateFrom < @dateTo)
BEGIN
SELECT @dateFrom = DATEADD(day, 1,@dateFrom)
INSERT INTO @dates
SELECT @dateFrom
END
END$$
DELIMITER ;
The syntax error is because you forgot the ; after each statement.
There are other issues:
@. Make them ordinary variables.VALUES rather than SELECT.DATEADD is not a MySQL function. It has DATE_ADD, but the syntax is different from what you used.DELIMITER $$
CREATE PROCEDURE proc()
BEGIN
DECLARE dateFrom datetime;
DECLARE dateTo datetime;
SET dateFrom = '2001-01-01';
SET dateTo = '2001-01-12';
CREATE TEMPORARY TABLE dates (
dt datetime
);
WHILE(dateFrom < dateTo)
BEGIN
SET dateFrom = DATE_ADD(dateFrom, INTERVAL 1 DAY);
INSERT INTO dates VALUES (dateFrom);
END WHILE;
END$$
DELIMITER ;