Create function on mysql

Question

I have the following on mysql:

DELIMITER //
CREATE FUNCTION dateDiff1(contract_id INT, cust_id INT)
RETURNS INT
BEGIN
DECLARE startDate, endDate DATETIME;
DECLARE result int;
SET startDate = (SELECT startDate FROM contract WHERE insurance_cover_id = contract_id AND customer_id = cust_id);
SET endDate = (SELECT endDate FROM contract WHERE insurance_cover_id = contract_id AND customer_id = cust_id);
SET result = (SELECT TIMESTAMPDIFF(MONTH, endDate, startDate));
RETURN result;
END;
//
DELIMITER ;

SELECT dateDiff1(1,1);

and it returns NULL, any suggestion?

BTW, this could be done in a single query: `RETURN (SELECT TIMESTAMPDIFF(MONTH, endDate, startDate) FROM contract WHERE insurance_cover_id = contract_id AND customer_id = cust_id);` — Barmar, Dec 18 '20 at 16:22
Don't use the same name for your variables as the column names. It's selecting the variables, not the columns. — Barmar, Dec 18 '20 at 16:23
Are you sure that data exists in your table for the contract_id 1 and cust_id 1? — Bill Karwin, Dec 18 '20 at 16:29
Are those columns in your table DATETIME columns? If they are storing dates as strings, they could be using an invalid date format, so they can't be used as arguments to TIMESTAMPDIFF(). — Bill Karwin, Dec 18 '20 at 16:29
If you want to debug this iteratively change what is returned until you find the problem. — P.Salmon, Dec 18 '20 at 16:47

score 0 · Answer 1 · answered Dec 18 '20 at 17:26

Maybe, you could solved your issue as follows as far as you got some data in your database:

CREATE FUNCTION datediff1(_contract_id INT [, _cust_id INT])
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE res INT;
    SELECT TIMESTAMPDIFF(MONTH, endDate, startDate) FROM contract WHERE contract_id = 
    _contract_id [AND cust_id = _cust_id] INTO res;
RETURN res;
END

I marked the cust_id related parts as optional since it is most likely that you contract_id is already referred to a particular customer. I hope it will help. Good luck.

Create function on mysql

1 Answers1