How do you exclude weekends from a date_sub?

Question

I am trying to use date_sub to subtract the current date - 11 days. I would like to exclude weekends.. this is what I have so far:

DATE_SUB(now(), INTERVAL 11 day)

not to sure how to exclude weekends... any help is appreciated.

Answer 1

This question is about subtracting working days. Assuming that weekend is Saturday-Sunday, we can write the solution as follows:

We know that:

• Every full week has 5 working days.
• Thus,
  • num_of_weeks = floor(@num_working_days / 5)
  • delta_days = @num_working_days % 5

So, a first approximation could be:

SET @num_working_days = 4; -- pick any integer
SET @num_days = 7 * FLOOR(@num_working_days / 5) - @num_working_days % 5;     
SELECT DATE_SUB(NOW(), INTERVAL @num_days DAY)

However, this will not work in the following and similar cases:

if today is Monday and num_working_days % 5 is 1, the above will errornously give you Sunday, when it should give you Friday.

Generally, it will fail if:

WEEKDAY(NOW()) - @num_working_days % 5 < 0

To account for that, an additional 2 days must be subtracted whenever this condition is met.

• overflow_days = 2 * (WEEKDAY(NOW()) - @num_working_days % 5 < 0)

So, the second approximation would be:

SET @num_working_days = 4;
SET @overflow_days = 2 * (WEEKDAY(NOW()) - @num_working_days % 5 < 0)
SET @num_days = 7 * FLOOR(@num_working_days / 5) - @num_working_days % 5;

SELECT DATE_SUB(NOW(), INTERVAL @num_days DAY)

Finally,

This will work as long as now() is not in a week-end day. For that case, you'd need to replace now() in the above formula with the previous week-ending date:

• weekend_correction = DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) % 5 DAY)

Which leads to the horrible looking but fully working:

SET @num_working_days = 4;
SET @weekend_correction = DATE_SUB(NOW(), INTERVAL WEEKDAY(NOW()) % 5 DAY);
SET @overflow_days = 2 * (WEEKDAY(@weekend_correction) - @num_working_days % 5 < 0);
SET @num_days = 7 * FLOOR(@num_working_days / 5) - @num_working_days % 5;

SELECT DATE_SUB(@weekend_correction, INTERVAL @num_days DAY); 

Now, in production, I recommend that you create a function on your MySQL server to encapsulate this logic, and you can call this function whenever you need to subtract working days.

Answer 2

SET @date = '2015.07.25';
SET @n =6;

SELECT DATE_SUB(
              @date,INTERVAL CASE 
                  WHEN DAYOFWEEK(@date)=1 THEN (@n +(FLOOR((@n-0.5)/5)+1)*2 - 1) 
                  WHEN DAYOFWEEK(@date)=2 THEN (@n +(FLOOR((@n-0.5)/5)+1)*2)
                  WHEN DAYOFWEEK(@date)=3 THEN (@n-1 +(FLOOR(((@n-1)-0.5)/5)+1)*2 + 1)
                  WHEN DAYOFWEEK(@date)=4 THEN (@n-2 +(FLOOR(((@n-2)-0.5)/5)+1)*2 + 2)
                  WHEN DAYOFWEEK(@date)=5 THEN (@n-3 +(FLOOR(((@n-3)-0.5)/5)+1)*2 + 3)
                  WHEN DAYOFWEEK(@date)=6 THEN (@n-4 +(FLOOR(((@n-4)-0.5)/5)+1)*2 + 4)
                  WHEN DAYOFWEEK(@date)=7 THEN (@n-5 +(FLOOR(((@n-5)-0.5)/5)+1)*2 + 5) 
             END DAY
);

@date is the date. You can give any date.

@n is the number of WORKING days you want subtracted.

The answer given here didn't work for me, it sometimes returned dates in the future, or gave results that fell on Sundays, which is not a working day so I made this SELECT. For example, if the date given falls on a Monday and you want 1 working day subtracted, it will return the date on last Friday. In this code, non-working days are considered to be Saturday and Sunday.

It should work for any Date and any number of WORKING days given.

Answer 3

This is the same method to calculate but just simplify the code :

SET @date = '2015.07.25';
SET @n =6;

SELECT DATE_SUB(
          @date,INTERVAL CASE
                    WHEN DAYOFWEEK(@date)=1 THEN (@n +(FLOOR((@n-0.5)/5)+1)*2 - 1)
                    WHEN DAYOFWEEK(@date)=2 THEN (@n +(FLOOR((@n-0.5)/5)+1)*2)
                    WHEN DAYOFWEEK(@date)>=3 THEN (@n-DAYOFWEEK(@date)-2 +(FLOOR(((@n-DAYOFWEEK(@date)-2)-0.5)/5)+1)*2 + DAYOFWEEK(@date)-2)
                    END DAY
);