GonHL GonHL - 5 months ago 13
SQL Question

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?

Answer

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;
Comments