CODEWITHSUNDEEP
mysqldatestored-procedures
GonHL
GonHL

Reputation: 67

MySQL fails to add days to a date when month is over

I am trying to get a final date when a number of days (pplazo input) has elapsed, starting on a date (pfecha input), and avoiding to count certain dates (feriado) that are listed on a table.

So far this is what i got to, using a Stored Procedure:

Input parameters:

pfecha -- DATE

pplazo -- INT (11)

PROC:BEGIN

DECLARE i INT(1);

START TRANSACTION;

SET i=1;

lazo:LOOP
    IF NOT EXISTS (SELECT * FROM feriados WHERE feriado=pfecha+i)
        THEN
        SET pfecha=pfecha+1;
        SET i=i+1;
    END IF;

    IF i=pplazo
        THEN
        LEAVE lazo;
    END IF;
END LOOP lazo;

COMMIT;

SELECT pfecha as respuesta;

END

The thing is, when the days to count make the date go pass the end of the month, then the "respuesta" turns to 0000-00-00. This shouldn't be happening, if I input 15 days starting on the 2016-04-20 then the resulting date should be something like 2016-05-5.

Can you spot my mistake? Could you point me in the right direction?

Upvotes: 0

Views: 31

Answers (1)

Barmar
Barmar

Reputation: 781370

The correct way to add a number of days to a date is with the DATE_ADD or ADDATE functions, not the + operator. See 

lazo:LOOP
    IF NOT EXISTS (SELECT * FROM feriados WHERE feriado=DATE_ADD(pfecha, INTERVAL i DAY))
        THEN
        SET pfecha=DATE_ADD(pfecha, INTERVAL 1 DAY);
        SET i=i+1;
    END IF;

    IF i=pplazo
        THEN
        LEAVE lazo;
    END IF;
END LOOP lazo;

Upvotes: 1

Related Questions