MySQL Stored Procedures - Adding integer variable to date returns null

Question

I am writing my first mysql procedure. I have a date variable and 2 integer variables. I am multiplying the two integer variables and adding the resultant integer to the date variable.

For example:

pint=6;
noftimes=3
sdate=2020-05-05
totDays=pint*noftimes
edate=2020-05-05+totDays

I am able to multiple pint*noftimes but not add sdate+totDays. Whereas If I add sdate+10 then I am getting a correct incremental date value. The following is my procedure:

DELIMITER $$
CREATE DEFINER=`pattu`@`localhost` PROCEDURE `getFieldWorkDates`(IN `p_id` INT, OUT `totDays` INT, OUT `edate` DATE)
BEGIN
DECLARE noftimes int;
DECLARE pint int;
DECLARE sdate DATE;
DECLARE tdays int;
SELECT startDate into sdate from projects where idprojects=p_id;
SELECT projectDuration into noftimes from projects where idprojects=p_id;
SELECT recFreq into pint from projects where idprojects=p_id;
SET totDays=pint*noftimes;
SET edate = sdate+(pint*noftimes);

END$$
DELIMITER ;

When I execute this, I am getting the message, your query has been executed successfully. 0 rows affected

@Akina Thank you...it worked. I can accept this as an answer. — Apricot
– Apricot, Commented May 5, 2020 at 14:02

Akina · Accepted Answer · 2020-05-05 14:07:27Z

1

SET edate = sdate+(pint*noftimes);

You cannot add integer to date. Use DATE_ADD() function.

CREATE 
DEFINER=pattu@localhost
PROCEDURE getFieldWorkDates ( IN p_id INT, 
                              OUT totDays INT, 
                              OUT edate DATE)
SELECT recFreq * projectDuration, 
       startDate + INTERVAL recFreq * projectDuration DAY 
INTO totDays, 
     edate 
FROM projects 
WHERE idprojects=p_id;

answered May 5, 2020 at 14:07

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Stored Procedures - Adding integer variable to date returns null

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related