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 WHILE;

    SET NewDate = ADDDATE(StartDate, Count);
    RETURN NewDate;

END;
$$

DELIMITER ;

I end up getting an error:

Error 1415: Not allowed to return a result set from a function

I can't seem to figure out where exactly it is trying to return a result set.

Is there an error in my syntax? Are there any better solutions?

Thanks!

EDIT

It appears MySQL doesn't have a DATEPART or DATEADD function. I see in the documentation that they have ADDDATE and DAYOFWEEK. Updated the code to represent this. I also changed the SELECT statements to SET (Makes sense now why I was getting the original error)

As a result I get a new error when attempting to run a query using the function via CF

[Table (rows 1 columns ADDWORKDAYS(10,"2013-09-01")): [ADDWORKDAYS(10,"2013-09-01"): coldfusion.sql.QueryColumn@7a010] ] is not indexable by ADDWORKDAYS(10

Just a guess: in the `SELECT NewDate = ...` near the end, try `SET` instead of `SELECT` - `SET NewDate = ...`. — Ed Gibbs, Jul 18 '13 at 18:38

Giovanni Guarino · Accepted Answer · 2015-05-26T10:32:46.320

10

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 ;

edited May 26 '15 at 10:32

answered Jul 18 '13 at 18:50

Giovanni Guarino

346
2
6

I dont actually have that function, looks like MySQL doesn't have DATEPART or DATEADD (their equivalent is ADDDATE) I updated my question to reflect this. – Jason Bristol Jul 18 '13 at 18:52
I have modified your function with mysql syntax. – Giovanni Guarino Jul 18 '13 at 19:34
This works great! Thanks! Have to just modify some of the conditional logic, `WHILE (i < WorkingDays) DO` does not account for negative input like I would like, however That shouldn't be too bad. Thank you again! – Jason Bristol Jul 18 '13 at 19:39
1

Minor note, but it'd be helpful for people just copying and pasting your function in to include the `DELIMITER ;` line at the end. Otherwise thanks for this answer! – quetzaluz May 19 '15 at 18:05

score 2 · Answer 2 · answered Oct 08 '15 at 22:28

This implementation is a bit more efficient then the accepted answer (probably not important), but also works for negative business days (was important for me).

The basic idea was every 5 days converts to 7 days, then you might need to adjust by adding or subtracting 2 days if the (days % 5) + the start day of the week is not a week day.

DROP FUNCTION IF EXISTS AddBusDays;
DELIMITER $$
CREATE FUNCTION AddBusDays
(
    WorkingDays INT,
    UtcStartDate DATETIME,
    TZ VARCHAR(1024) 
)
RETURNS DATETIME

BEGIN
DECLARE RealOffset INT;
DECLARE StartDate DATETIME;
DECLARE Adjustment INT;

SELECT CONVERT_TZ(UtcStartDate, 'UTC', TZ) into StartDate;

select case when WorkingDays >=0 then 2 else -2 end into Adjustment;

select 
    case when (WorkingDays >= 0 AND DAYOFWEEK(StartDate) + (WorkingDays %  5) > 6) OR (WorkingDays < 0 AND DAYOFWEEK(StartDate) + (WorkingDays %  5) < 2)
    then (WorkingDays %  5) + Adjustment + (WorkingDays DIV 5) * 7
    else WorkingDays %  5 + (WorkingDays DIV 5) * 7
    end into RealOffset;
return CONVERT_TZ(date(adddate(StartDate, RealOffset)), TZ, 'UTC');
END;
$$

DELIMITER ;

score 1 · Answer 3 · edited Mar 01 '16 at 12:04

-- This is exact query which adds no of business days to date (Exclude Saturday and Sunday)   
 DROP FUNCTION IF EXISTS DateAddBusiness;
    DELIMITER ||
    CREATE FUNCTION DateAddBusiness(mydate DATE, numday INT) 
    RETURNS DATE
    DETERMINISTIC
    COMMENT 'Adds business days between two dates'
    BEGIN
     DECLARE num_week INT DEFAULT 0;
     DECLARE num_day INT DEFAULT 0;
     DECLARE adj INT DEFAULT 0;
     DECLARE total INT DEFAULT 0;
     SET num_week = numday DIV 5;
     SET num_day = MOD(numday, 5);

     IF (DAYOFWEEK(mydate)=6 || DAYOFWEEK(mydate)=5 || DAYOFWEEK(mydate)=4 ) then
      SET adj = 2;
     END IF;

     IF (DAYOFWEEK(mydate)=7 ) then
      SET adj = 1;
     END IF;

     SET total = adj + num_day;
     RETURN DATE_ADD(mydate, INTERVAL total DAY);
    END
    ||
    DELIMITER ;


    -- Unit testing queries
    select DateAddBusiness("2015-10-19","3") // 22
    select DateAddBusiness("2015-10-20","3") // 23

    select DateAddBusiness("2015-10-21","3") // 26
    select DateAddBusiness("2015-10-22","3") // 27
    select DateAddBusiness("2015-10-23","3") // 28
    select DateAddBusiness("2015-10-24","3") //28
    select DateAddBusiness("2015-10-25","3") //28
    select DateAddBusiness("2015-10-26","3") //29

score 1 · Answer 4 · edited Jun 20 '20 at 09:12

I modified the version given here to accept both positive and negative days. None of the other answers were doing it for me so this is the most efficient solution I came up with.

DROP FUNCTION IF EXISTS WORKDAY_ADD;
DELIMITER &&
CREATE FUNCTION WORKDAY_ADD(mydate DATE, numday INT) RETURNS DATE
BEGIN
DECLARE num_week INT DEFAULT 0;
DECLARE num_day INT DEFAULT 0;
DECLARE adj INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
SET num_week = ABS(numday DIV 5);
SET num_day = MOD(numday, 5);

IF (WEEKDAY(DATE_ADD(mydate, INTERVAL num_day DAY)) >= 5) THEN 
    SET adj = 2;
END IF;

SET total = (num_week * 7 + adj + ABS(num_day));

IF numday < 0 THEN
    SET total = total * -1;
END IF;

RETURN DATE_ADD(mydate, INTERVAL total DAY); 
    
END&&
DELIMITER ;

Usage

Add five business days: SELECT WORKDAY_ADD('2016-02-18', 5)
Subtract two business days: SELECT WORKDAY_ADD('2016-02-18', -2)

score 0 · Answer 5 · answered Jul 18 '13 at 19:33

0

In this post there is a function that made what you asks. I think that function can help you.

answered Jul 18 '13 at 19:33

Hernandcb

530
8
20

score 0 · Answer 6 · edited Jun 15 '17 at 12:31

CREATE FUNCTION `WORKDAY_ADD`(exp_date DATE, days_to_add SMALLINT) 
RETURNS date
     DETERMINISTIC
BEGIN
    DECLARE res_date DATE;
    DECLARE day_count TINYINT;

SET res_date = date_add(exp_date, INTERVAL 1 DAY); 
IF DAYNAME(res_date)='Monday' OR DAYNAME(res_date)='Tuesday' OR DAYNAME(res_date)='Wednesday' THEN
    SET res_date = date_add(exp_date, INTERVAL days_to_add DAY);
ELSEIF DAYNAME(res_date)='Thursday' OR DAYNAME(res_date)='Friday' OR DAYNAME(res_date)='Saturday' THEN
    SET res_date = date_add(exp_date, INTERVAL days_to_add+2 DAY);        
ELSEIF DAYNAME(res_date)='Sunday' THEN
    SET res_date = date_add(exp_date, INTERVAL days_to_add+1 DAY); 
END IF;
  RETURN res_date;
END;

score 0 · Answer 7 · answered Nov 13 '17 at 11:14

FUNCTION `WORKDAY_ADD`(mydate DATE, numday INT) RETURNS date
BEGIN
DECLARE num_day INT DEFAULT 0;
DECLARE adj INT DEFAULT 0;
DECLARE total INT DEFAULT 0;
SET num_week = ABS(numday DIV 5);
SET num_day = MOD(numday, 5);

IF  numday > 0 and (WEEKDAY(DATE_ADD(mydate, INTERVAL num_day DAY)) < WEEKDAY(mydate)) THEN
    SET adj = 2;
END IF;

IF  numday < 0 and (WEEKDAY(DATE_ADD(mydate, INTERVAL num_day DAY)) > WEEKDAY(mydate)) THEN
    SET adj = 2;
END IF;

SET total = (num_week * 7 + adj + ABS(num_day));

IF numday < 0 THEN
    SET total = total * -1;
END IF;

RETURN DATE_ADD(mydate, INTERVAL total DAY); 

END

Hi all, needed a formula that would go backwards and forwards. I used one above but spotted an error. I have corrected it now and the formula is working well.

score 0 · Answer 8 · edited Sep 25 '19 at 08:32

0

FUNCTION `Addworkday`(workday int(5),dt date) RETURNS date        
begin
    declare count int;        
    declare i int;        
    declare y date;        
set count=0;       
while count<workday do        
    set count=count+1;        
    set dt=date_add(dt,interval 1 day);        
    while dayofweek(dt) in (1,7) do        
        set dt=date_add(dt,interval 1 day);        
    end while;        
end while;        
return dt;        
end

edited Sep 25 '19 at 08:32

Suraj Rao

29,388
11
94
103

answered Sep 25 '19 at 08:29

Sally Liu

1

3

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Suraj Rao Sep 25 '19 at 08:33

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

8 Answers8

Usage

Linked