CODEWITHSUNDEEP
mysqlstored-functions
Vasu
Vasu

Reputation: 69

Mysql syntax error creating stored function 5.6.10

I am getting a syntax error on executing the below function in MySQL.

DELIMITER $$

DROP FUNCTION IF EXISTS `WORKDAYS` $$
CREATE FUNCTION `WORKDAYS` (start_date DATETIME, end_date DATETIME) 
RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN 
 DECLARE days INT; 
 SET days = 0;
 REPEAT
 SET days = days + (CASE WHEN DAYNAME(start_date) in ('Friday', 
                      'Saturday', 'Sunday') THEN 0  ELSE 1 END);
 SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);
 UNTIL start_date > end_date END  REPEAT;     
 RETURN days;
END $$
DELIMITER ;

Error I get: [ERROR in query 2] 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 'RETURN days; END' at line 13

Upvotes: 0

Views: 104

Answers (1)

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

Hidden character in your query after the END REPEAT;. Make sure your editor is UTF-8.

DELIMITER $$

DROP FUNCTION IF EXISTS `WORKDAYS`$$

CREATE FUNCTION `WORKDAYS` (start_date DATETIME, end_date DATETIME) 
RETURNS INT
LANGUAGE SQL
DETERMINISTIC

BEGIN 
    DECLARE days INT; 
    SET days = 0;
    REPEAT
        SET days = days + (CASE WHEN DAYNAME(start_date) in ('Friday','Saturday', 'Sunday') THEN 0 ELSE 1 END);
        SET start_date = DATE_ADD(start_date, INTERVAL 1 DAY);
    UNTIL start_date > end_date END REPEAT;
RETURN days;
END$$

DELIMITER ;

Upvotes: 1

Related Questions