Hello all.
I have a funtion in MYSQL to substruct days from a date.
This function seamsto work but some dates it times out.
This is my function

CREATE DEFINER=`root`@`localhost` FUNCTION `Calculater`(intrval int, req date) RETURNS date
    DETERMINISTIC
BEGIN
declare calculated date;
declare added int default  0;

Lbl_Loop: Loop
IF added=intrval then
leave lbl_loop;
End IF;
set calculated = DATE_SUB(req,interval 1 day);
IF weekday(calculated)<5  then
set added= added+1;
End If;
end loop;
RETURN calculated;
END

If I do select Calculater(1,"2020-02-04"); it works, but select Calculater(1,"2020-02-02"); it runs for a few seconds and My SQL times out.

Thanks in advance.

Have you tried using EXPLAIN to see if you need any indexes?

Have you noticed whether the dates that work vs those that don’t are the ones where you are incrementing the added field?

I hve Solved it by replacing loop with While Do

FUNCTION `Calculater`(intrval int, req date) RETURNS date
    DETERMINISTIC
BEGIN
declare calculated date;
declare added int default  0;

WHILE added <intrval do
set calculated = DATE_SUB(req,interval 1 day);
set req=calculated;
IF weekday(calculated)<5  then
set added= added+1;
End If;
end while;
RETURN calculated;
END
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.