Trigger gives error with date

Question

I made a Trigger to count the working days. It accepts the dates of the month 06 very well, but other dates gives the following error:

INSERT INTO `teste` VALUES ("2018-05-01")
#1292 - Incorrect date value: '20180532' for column 'i' at row 1

Trigger code:

BEGIN
    DECLARE i date;
    DECLARE j int;

    SET i = DATE(NEW.dataAntiga);
    SET j = 0;

    WHILE (date(i) < NOW()) DO
        if WEEKDAY(date(i)) != 5 AND WEEKDAY(date(i)) != 6 THEN
            SET j = j + 1;
        END IF;
        SET i = date(i)+1;
    END WHILE;
    insert into teste2 values (j);
END

Test Table:

table test2:

Answer 1

If you do this:

SELECT DATE("2018-05-31") + 0; 

The result will be this:

20180531

If you do that:

SELECT DATE("2018-05-31") + 1;

Give it:

20180532

That’s the problem. When the i goes to the day 31 of May, you end up generating a 32 of May that does not exist. The date is converted into numeral before the sum, but you do not want to add numerals but add dates.

Mysql has the function DATE_ADD to add dates. For example:

SELECT DATE_ADD(DATE("2018-05-31"), INTERVAL 1 DAY);

Give it:

2018-06-01

So your Rigger should look like this:

BEGIN
    DECLARE i date;
    DECLARE j int;

    SET i = DATE(NEW.dataAntiga);
    SET j = 0;

    WHILE (DATE(i) < NOW()) DO
        if WEEKDAY(DATE(i)) != 5 AND WEEKDAY(DATE(i)) != 6 THEN
            SET j = j + 1;
        END IF;
        SET i = DATE_ADD(DATE(i), INTERVAL 1 DAY);
    END WHILE;
    INSERT INTO teste2 VALUES (j);
END

I also think that if you change all the DATE(i) simply by i, should work. After all, i is already of the type DATE.