Mysql syntax error creating stored function 5.6.10

Question

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

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 — Vasu
– Vasu, Commented May 10, 2018 at 19:34
Edit your question and add the full text of the error message — Sloan Thrasher
– Sloan Thrasher, Commented May 10, 2018 at 19:34
@SloanThrasher Thank you .. added the error message to the question — Vasu
– Vasu, Commented May 10, 2018 at 19:35
You're not actually running the sql with the `` before the first DELIMITER statement and after the last one, are you? — Uueerdo
– Uueerdo, Commented May 10, 2018 at 19:40
Just checked the syntax and found an odd character that displays as a space. Cleaned up the spaces and it checked out fine. I'll post an answer with the cleaned text — Sloan Thrasher
– Sloan Thrasher, Commented May 10, 2018 at 19:47

Nick · Accepted Answer · 2018-05-11 02:34:23Z

1

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 ;

edited May 11, 2018 at 2:34

Nick

147k23 gold badges67 silver badges106 bronze badges

answered May 10, 2018 at 19:50

Sloan Thrasher

5,0383 gold badges26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vasu Over a year ago

Thank you soo much for cleaning out the query @Sloan Thrasher

Collectives™ on Stack Overflow

Mysql syntax error creating stored function 5.6.10

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related