mardi 22 décembre 2015

Error in defining MySQL function with IF-END IF

I am trying to get write a function in MySQL which will give me the difference in two days excluding the weekends and custom defined holidays. My END IF statement is highlighted in RED indicating a syntax error. Can anyone help with this ?

CREATE 
FUNCTION `GetWorkingDays_FromDateRange`(date1 DATE, date2 DATE) RETURNS int(11)
BEGIN

DECLARE dateOne  DATE;
DECLARE dateTwo  DATE;

IF date1>date2  
    THEN
        SET dateTwo = date1;
        SET dateOne = date2;
    ELSE
        SET dateTwo = date2;
        SET dateOne = date1;
END IF;

DECLARE i INT;
SET i = (SELECT COUNT(*) from holidays where calendar_date BETWEEN dateOne AND dateTwo);

RETURN ABS(DATEDIFF(dateTwo, dateOne)) + 1
     - ABS(DATEDIFF(ADDDATE(dateTwo, INTERVAL 1 - DAYOFWEEK(dateTwo) DAY),
                    ADDDATE(dateOne, INTERVAL 1 - DAYOFWEEK(dateOne) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(dateOne < dateTwo, dateOne, dateTwo)) = 1)
     - (DAYOFWEEK(IF(dateOne > dateTwo, dateOne, dateTwo)) = 7)
     - i;
END

Aucun commentaire:

Enregistrer un commentaire