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:
x
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
Advertisement
Answer
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
orSun
, it must be added additional 2 days.