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