newby how to increment dates in a stored program

[daffy2]

Simple as it might seem this has defeated me so far.
I want to enter sequential dates for say 1 year in a table by means of a stored program.
I can attach one of my many effoorts if it helps.
Using a date type it stope at the end of the current month.
[code=mysql]
DROP PROCEDURE `adddates`//
CREATE DEFINER=`root`@ `localhost` PROCEDURE `adddates`()
begin
declare len int;
declare dd datetime;
declare aday int;
set aday = 24*3600;
set len = 100;
set dd = now();
while len > 0 do
insert into counter2(thedat e) values (date(dd));
set dd = dd + aday;
set len = len-1;
end while;
end
[/code]
Any helpful suggestions welcome
--
Dave B

[Atli]

Hi Dave.

You could use the DATE_ADD function to help you with this.

Try making your while loop look something like:
[code=mysql]
SET currentDate = startDate;
WHILE currentDate < endDate DO
INSERT INTO myTable(DateFie ld) VALUES(currentD ate);
SET currentDate = DATE_ADD(curren tDate, INTERVAL 1 DAY);
END WHILE;[/code]
Where startDate, endDate and currentDate are all DATE or DATETIME types.
You could set the startDate using the NOW function and use DATE_ADD to set the end date.

[daffy2]

Thanks

Thanks for that;
I looked up 'interval' but missed the 'date-add' function. I am used to Firebird where adding 1 to a date gets the next day !
OK Back to the drawing board.
--
Dave