MYSQL Add working days to date

Question

I want to add 5 days to the provided date, but the calculation must skip weekends.

I already know how to add 5 days without skipping weekends:

SELECT DATE_ADD(`date_field`, INTERVAL 5 DAY) As FinalDate
FROM `table_name`;

Now I want the returned value to skip weekends.

Currently if date_field = 2016-07-22 the results will be 2016-07-27
But I want the results to be 2016-07-29

Answer 1

Try this:

SELECT DATE_ADD(
    date_field,
    INTERVAL 5 + 
    IF(
        (WEEK(date_field) <> WEEK(DATE_ADD(date_field, INTERVAL 5 DAY)))
        OR (WEEKDAY(DATE_ADD(date_field, INTERVAL 5 DAY)) IN (5, 6)),
        2,
        0)
    DAY
    ) AS FinalDate
FROM `table_name`;

How it works:

• Firstly, it will add 5 days on your date.
• Secondly, when date_field and 5 days later are in two different weeks, it must be added additional 2 days.
• Thirdly, when 5 days later is Sat or Sun, it must be added additional 2 days.

Answer 2

This can easily be done for an arbitrary amount of days with a recursive CTE:

WITH RECURSIVE a AS (
    SELECT
        CURRENT_DATE date,  -- Start date
        0 days
    UNION
    SELECT
        -- Always increment the date
        a.date + INTERVAL 1 DAY AS date,
        -- Increment the work day count only if it's not a weekend day
        a.days + (WEEKDAY(a.date + INTERVAL 1 DAY) < 5) AS days
    FROM a
    WHERE
        -- Keep going until the week day count reaches 10
        a.days < 10  -- Amount of days to add
)
SELECT MAX(date)
FROM a

In the example situation you would use a subquery:

SELECT
    (
        WITH RECURSIVE a AS (
            SELECT
                date_field date,
                0 days
            UNION
            SELECT
                a.date + INTERVAL 1 DAY AS date,
                a.days + (WEEKDAY(a.date + INTERVAL 1 DAY) < 5) AS days
            FROM a
            WHERE a.days < 5
        )
        SELECT MAX(date)
        FROM a
    ) AS final_date
FROM table_name

Answer 3

I did try your solution but faced a problem when using it with a larger interval (e.g 20 days). It works perfectly with little intervals though.

Example : for '2017-10-04' + 20 days, your algorithm return '2017-10-26'. It should be '2017-11-01' since we skip 4 weekends.

The numbers of days you add isn't calculated depending on the difference between the 2 week #, so the maximum days you can add is 2 and in my case, it should be 8 (4x2).

I modified your code to end up with this (I also add variables, much more convenient to modify)

SELECT 
@ID:='2017-10-04' as initial_date, -- the initial date in the right format to manipulate (add x day)
@DTA:=20 as days_to_add, -- number of days to add
@DA:= DATE_ADD(@ID, INTERVAL @DTA DAY) as date_add,
@LASTDAY := WEEKDAY(@DA) as last_day, -- the day (Monday, Tuesday...) corresponding to the initial date + number of days to add
@WEEK1 := DATE_FORMAT(@ID, '%v') as initial_date_week, -- format the initial date to match week mode 3 (Monday 1-53)
@WEEK2 := DATE_FORMAT(@DA, '%v') as added_date_week_nbr, -- the week # of the initial_date + number of days to add
@WEEKDIFF := @WEEK2 - @WEEK1 as week_difference, -- the difference between week 2 and week 1
DATE_ADD(@ID,
        INTERVAL @DTA + 
            if ( @WEEKDIFF > 0 or @LASTDAY in (5,6),
              2,
              0
              ) + 
             if (@WEEKDIFF > 1,
             @WEEKDIFF*2,
             0
             ) DAY
    ) AS FinalDate

The way I get my week numbers can seems weird but this is because I'm running this in France and my DB seems to be configured in a way that weeks are natively starting by Sunday, "%v" represent the 'mode 3' for weeks, you can check the MySQL documentation here for more details : https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html (ctrl + F > '%v')

I didn't implement the public holiday yet, but I'm thinking of adding X days in the calculation each times one of this day is in the period we're looking at.

According to my (few) tests, this should work. Let me know if not

Answer 4

try this out, should work nicely, basically loop through each of the days and check if they are saturday or sunday, ignore them.

https://social.technet.microsoft.com/wiki/contents/articles/30877.t-sql-extending-dateadd-function-to-skip-weekend-days.aspx

Answer 5

Plus hollydays 1 or 2

select GREATEST(WEEKDAY(NOW()) - 4, 0) 'hollydays'

Answer 6

My solution is to create a function that returns the calculated date, it will consider the consecutive weekends:

DELIMITER $$
CREATE FUNCTION `add_working_days_to_current_month`(ndays INT) RETURNS DATE
NO SQL 
BEGIN

    declare finalDate, startDate, originalFinalDate DATE;
    declare weekNumberStartDate, weekNumberEndDate, weekDiff INT;
    
    set startDate = DATE(CONCAT(YEAR(DATE_SUB(current_date(), INTERVAL 1 MONTH)),"-",MONTH(DATE_SUB(current_date(), INTERVAL 1 MONTH)),"-",DAY(LAST_DAY(DATE_SUB(current_date(), INTERVAL 1 MONTH)))));
    set weekNumberStartDate = WEEK(startDate);
    set finalDate = DATE_ADD(startDate, INTERVAL ndays DAY);
    set originalFinalDate = finalDate;
    set weekNumberEndDate = WEEK(finalDate);
    
    IF(weekNumberEndDate != weekNumberStartDate) THEN
        set weekDiff = (weekNumberEndDate - weekNumberStartDate) * 2;
        set finalDate = DATE_ADD(finalDate, INTERVAL weekDiff DAY);
    END IF;
    
    set weekNumberStartDate = WEEK(originalFinalDate);
    set weekNumberEndDate = WEEK(finalDate);
    IF(weekNumberEndDate != weekNumberStartDate) THEN
        set weekDiff = (weekNumberEndDate - weekNumberStartDate) * 2;
        set finalDate = DATE_ADD(finalDate, INTERVAL weekDiff DAY);
    END IF;
    
    IF(WEEKDAY(finalDate) IN (5, 6)) THEN
        set finalDate = DATE_ADD(finalDate, INTERVAL 2 DAY);
    END IF;
    
    
return finalDate;
END$$
DELIMITER ;

Basically i'm using the same logic as the accepted answer but acumulating the weekends. So for each weekend i will add 2 days and at the end i will if the result date is on weekend i will add 2 more

Answer 7

WHERE datefield BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL 7 DAY
AND WEEKDAY(datefield) NOT IN (5,6);