Need help to check and modify my SQL code which is aimed to find the next working date ( I wrote thi-CodePudding

CREATE FUNCTION NewStartDate (startdate DATE)
RETURNS DATE DETERMINISTIC
BEGIN

    DECLARE nextdate DATE;
    DECLARE counter integer;
    
    SET COUNTER = 0
    SET nextdate = startdate
    
    WHILE (nextdate in (select `dt` from `calendar2` where `IsHoliday`=1) LOOP
    
        nextdate = DATE_ADD(startdate,INTERVAL counter)
        counter=counter 1
        
    END LOOP
    
    RETURN nextdate;
END

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'loop nextdate = DATEADD(nextdate,counter); IF nextdate in (select dt FROM `c' at line 9

CodePudding user response：

I'm afraid the code you provided have more than one syntax error. Notably, you did not specify the unit for the interval in your date_add function DATE_ADD(startdate,INTERVAL counter) . I have revised your code in workbench and tested it (using a different while condition). It works now.

delimiter //
drop function if exists NewStartDate//
CREATE FUNCTION NewStartDate (startdate DATE)
RETURNS DATE DETERMINISTIC
BEGIN

    DECLARE nextdate DATE;
    DECLARE counter integer;
    
    SET COUNTER = 0 ;
    SET nextdate = startdate ;
    
    WHILE (nextdate in (select `dt` from `calendar2` where `IsHoliday`=1)) do
    
       set nextdate = DATE_ADD(startdate,INTERVAL   counter day);
       set counter=counter 1;
        
    END WHILE ;
    
    RETURN nextdate;
END //