The Flash The Flash - 4 months ago 21
MySQL Question

MYSQL Add working days to date

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

JPG JPG
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 or Sun, it must be added additional 2 days.