CODEWITHSUNDEEP
mysql
Terry Carmen
Terry Carmen

Reputation: 3886

MySQL Stored Procedure Date Not Incrementing, loop not terminating

I'm trying to insert a sequence of datetimes into a table. The times are supposed to increment by 15 minutes, so I get, for example

However what I actually get is

This is a really simple loop and I just can't see what I'm missing here.

The code is from another post on SO.

Thanks,

Terry

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `FillCalendar`(IN `start_date` DATE, IN `end_date` DATE)

BEGIN
    DECLARE crt_date DATE;
    SET crt_date = start_date;
    WHILE crt_date <= end_date DO
        INSERT IGNORE INTO Schedule(StartTime) VALUES(crt_date);
        SET crt_date = ADDDATE(crt_date, INTERVAL 15 MINUTE);
    END WHILE;
    END$$
DELIMITER ;

Upvotes: 0

Views: 64

Answers (1)

Barmar
Barmar

Reputation: 781130

You need to declare the variable as DATETIME so that it can hold the time of day. DATE can only hold a date, and the time is always 00:00.

DECLARE crt_date DATETIME;

You may want to change the procedure parameters to be DATETIME as well.

Upvotes: 3

Related Questions