MYSQL添加最新工作日 [英] MYSQL Add working days to date
本文介绍了MYSQL添加最新工作日的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想在提供的日期前加上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
orSun
, it must be added additional 2 days.
这篇关于MYSQL添加最新工作日的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文