MYSQL添加最新工作日 [英] MYSQL Add working days to date

问题描述

我想在提供的日期前加上5天，但计算必须跳过周末.

I want to add 5 days to the provided date, but the calculation must skip weekends.

我已经知道如何添加5天而不跳过周末:

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.

当前，如果date_field = 2016-07-22，结果将为2016-07-27
但我希望结果为2016-07-29

Currently if date_field = 2016-07-22 the results will be 2016-07-27
But I want the results to be 2016-07-29

推荐答案

尝试一下:

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`;

工作原理:

• 首先，它将在您的约会上增加5天.
• 第二，当date_field和5天后在两个不同的星期内时，必须再添加2天.
• 第三，如果5天后是Sat或Sun，则必须再添加2天.

• 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.

这篇关于MYSQL添加最新工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！