MySQL Function to add a number of working days to a DATETIME

Question

I need a MySQL Function that will allow me to pass a number of working days (Monday - Friday) and a start DATE or DATETIME (doesn't matter for my implementation), and have it return a new DATE or DATETIME that many work days in the future. Example: `SELECT AddWorkDays(10, "2013-09-01")` returns "2013-09-16" assuming "2013-09-01" is a Monday. Similarly: `SELECT AddWorkDays(-10, "2013-09-16")` returns "2013-09-01" I found this function for an MSSQL database (I think) that is exactly what I need except its not in MySQL. I tried to manually convert it into MySQL syntax and got about this far: ``` DROP FUNCTION IF EXISTS AddWorkDays; DELIMITER $$ CREATE FUNCTION AddWorkDays ( WorkingDays INT, StartDate DATE ) RETURNS DATE BEGIN DECLARE Count INT; DECLARE i INT; DECLARE NewDate DATE; SET Count = 0; SET i = 0; WHILE (i < WorkingDays) DO BEGIN SET Count = Count + 1; SET i = i + 1; WHILE DAYOFWEEK(ADDDATE(StartDate, Count)) IN (1,7) DO BEGIN SET Count = Count + 1; END; END WHILE; END; END WHIL…

Accepted Answer

This is new function with mysql syntax:

```
DROP FUNCTION IF EXISTS AddWorkDays;
DELIMITER $$
CREATE FUNCTION AddWorkDays
(
    WorkingDays INT,
    StartDate DATETIME
)
RETURNS DATETIME

BEGIN
    DECLARE Count INT;
    DECLARE i INT;
    DECLARE NewDate DATETIME;
    SET Count = 0;
    SET i = 0;

WHILE (i < WorkingDays) DO
        BEGIN
            SELECT Count + 1 INTO Count;
            SELECT i + 1 INTO i;
            WHILE DAYOFWEEK(DATE_ADD(StartDate,INTERVAL Count DAY)) IN (1,7) DO
                BEGIN
                    SELECT Count + 1 INTO Count;
                END;
            END WHILE;
        END;
    END WHILE;

SELECT DATE_ADD(StartDate,INTERVAL Count DAY) INTO NewDate;
    RETURN NewDate;

END;
$$

DELIMITER ;
```

MySQL Function to add a number of working days to a DATETIME

Solution

Problem