Return value from DATE_SUB/DATE_ADD in stored function

Question

I am trying to write a stored function that takes a string in proper ISO format (yyyy-mm-dd) and subract a certain number of weekdays from it. Based off of this question here I have tried both the accepted answer as well as one a few answers down that does things differently, however, both of them are just saying how to write the pure sql, and don't have an example of a function.

What I currently have is this:

delimiter //
CREATE DEFINER=`root`@`localhost` FUNCTION `WEEKDATE_SUB` (days TINYINT, date_val VARCHAR(16))
RETURNS DATE DETERMINISTIC
BEGIN
    DECLARE SUBVAL INT;
    DECLARE dow INT;
    CASE
            WHEN dow=1 THEN SET SUBVAL = (days +(FLOOR((days-0.5)/5)+1)*2 - 1);
            WHEN dow=2 THEN SET SUBVAL = (days +(FLOOR((days-0.5)/5)+1)*2);
            WHEN dow=3 THEN SET SUBVAL = (days-1 +(FLOOR(((days-1)-0.5)/5)+1)*2 + 1);
            WHEN dow=4 THEN SET SUBVAL = (days-2 +(FLOOR(((days-2)-0.5)/5)+1)*2 + 2);
            WHEN dow=5 THEN SET SUBVAL = (days-3 +(FLOOR(((days-3)-0.5)/5)+1)*2 + 3);
            WHEN dow=6 THEN SET SUBVAL = (days-4 +(FLOOR(((days-4)-0.5)/5)+1)*2 + 4);
            WHEN dow=7 THEN SET SUBVAL = (days-5 +(FLOOR(((days-5)-0.5)/5)+1)*2 + 5);
    END CASE
    RETURN DATE_SUB(date_val, INTERVAL SUBVAL DAY);
END;//

When I try to add it, I get a vague error (as mysql is fond of offering): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RETURN DATE_SUB(date_val, INTERVAL SUBVAL DAY); END' at line 14

I have tried several variations on returning including trying to define a variable for Date sub and return that, but it's pretty much the same error.

Outside of a function, I know this works, so it seems like I should just be able to return that.

SELECT DATE_SUB("2016-01-01", INTERVAL 4 DAY);

score 1 · Answer 1 · answered Feb 09 '17 at 18:26

1

The docs suggest you need to have END CASE, instead of END

Additionally, the stored function will probably execute a little faster if you use the CASE expr WHEN value2 THEN .... WHEN value2 THEN ... END CASE version, since it will not have to repeat the DAYOFWEEK function calls potentially 7 times.

answered Feb 09 '17 at 18:26

Uueerdo

15,723
1
16
21

I gave your suggestions a shot. I am glad you caught the DAYOFWEEK thing. It's still giving the same error though. – S. Buda Feb 09 '17 at 18:32
got it. You set me on the right track. along with the END CASE, I needed a ; – S. Buda Feb 09 '17 at 18:45

score 1 · Answer 2 · answered Feb 09 '17 at 19:23

you can also use something like this

delimiter //
CREATE DEFINER=`root`@`localhost` FUNCTION `WEEKDATE_SUB` (date_val VARCHAR(10), days TINYINT)
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
    RETURN  date_val - INTERVAL
            FLOOR(days/5)*7 + 
            IF(DAYOFWEEK(date_val)-1 <= days - FLOOR(days/5)*5
              , (days - FLOOR(days/5)*5)+2
              , days - FLOOR(days/5)*5
           ) DAY;
END;//

samples

mysql> SELECT WEEKDATE_SUB('2017-02-06',1);
+------------------------------+
| WEEKDATE_SUB('2017-02-06',1) |
+------------------------------+
| 2017-02-03                   |
+------------------------------+
1 row in set (0,00 sec)

mysql> SELECT WEEKDATE_SUB('2017-02-07',1);
+------------------------------+
| WEEKDATE_SUB('2017-02-07',1) |
+------------------------------+
| 2017-02-06                   |
+------------------------------+
1 row in set (0,00 sec)

mysql> SELECT WEEKDATE_SUB('2017-02-07',2);
+------------------------------+
| WEEKDATE_SUB('2017-02-07',2) |
+------------------------------+
| 2017-02-03                   |
+------------------------------+
1 row in set (0,00 sec)

mysql>

S. Buda · Answer 3 · 2017-02-09T19:06:41.400

This is what I finally needed to get this working. Notice, the ; and the change to how I am using the CASE statement.

delimiter //
CREATE DEFINER=`root`@`localhost` FUNCTION `WEEKDATE_SUB` (date_val VARCHAR(10), days TINYINT)
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
    DECLARE SUBVAL INT;
    DECLARE dow INT;
    SET dow = DAYOFWEEK(date_val);
    CASE dow
            WHEN 1 THEN SET SUBVAL = (days   +(FLOOR((days-0.5)/5)+1)*2 - 1);
            WHEN 2 THEN SET SUBVAL = (days   +(FLOOR((days-0.5)/5)+1)*2);
            WHEN 3 THEN SET SUBVAL = (days-1 +(FLOOR(((days-1)-0.5)/5)+1)*2 + 1);
            WHEN 4 THEN SET SUBVAL = (days-2 +(FLOOR(((days-2)-0.5)/5)+1)*2 + 2);
            WHEN 5 THEN SET SUBVAL = (days-3 +(FLOOR(((days-3)-0.5)/5)+1)*2 + 3);
            WHEN 6 THEN SET SUBVAL = (days-4 +(FLOOR(((days-4)-0.5)/5)+1)*2 + 4);
            WHEN 7 THEN SET SUBVAL = (days-5 +(FLOOR(((days-5)-0.5)/5)+1)*2 + 5);
            ELSE SET SUBVAL = days;
    END CASE;
    RETURN DATE_SUB(date_val, INTERVAL SUBVAL DAY);
END;//

Return value from DATE_SUB/DATE_ADD in stored function

3 Answers3