How to exclude weekends from DATE_SUB in mysql

Question

Does anyone know how to exclude weekends from the DATE_SUB function in MYSQL? I am looking to search the past 1500 working days from CURDATE() but am struggling with the solution?

Much appreciate any guidance.

I recognise this question was asked on stackoverflow before (see link below) but I could not get his solution to work

How do you exclude weekends from a date_sub?

1500 working days is 300 * 5 working days/week = 300 weeks = 300 * 7 days = 2100 days. For 1501 to 1504 working days in the past count 1 to 4 working days in the past (use current day of week, skip the weekend) then subtract 2100 days (300 complete weeks) as explained above. Use the same idea with any number of working days. — axiac, Jul 04 '15 at 11:00
The solution does not work, it gives the incorrect date. E.g. if you use @num_working_days =4 you get 2015-07-08, you would expect 30/06/2015. Axiac I am trying to make sense what your saying. Unfortunately I am after a more dynamic function. — , Jul 04 '15 at 11:04

score 1 · Accepted Answer · edited Jul 27 '15 at 12:23

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

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

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

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

The answer given didn't work for me, it sometimes even gave dates in the future, it returned dates that were sundays or other errors like that.

I believe the last paragraph refers to the other question that is linked in the question. Maybe you should clarify this. — simbabque, Jul 27 '15 at 12:24

How to exclude weekends from DATE_SUB in mysql

1 Answers1