mysql add days,MYSQL添加工作日至今

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

解决方案

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.