Trigger gives error with date

Question

Trigger gives error with date

Navigation

#1 by (1 votes)

1

I did a trigger to count the weekdays. It accepts the dates of 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:

test2table:

mysql sql trigger

asked by anonymous 29.06.2018 / 22:32

1 answer

Flask SQLAlchemy: KeyError V-for access and display list [closed]

score 1 · Accepted Answer

If you do this:

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

The result will be this:

20180531

If you do this:

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

Give this:

20180532

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

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

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

Give this:

2018-06-01

So your trigger 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 DATE(i) simply by i , it should work. After all, i is already type DATE .