MySQL函数查找两个日期之间的工作日数 [英] MySQL function to find the number of working days between two dates

问题描述

Excel具有NETWORKDAYS()函数，该函数可以查找两个日期之间的工作日数.

Excel has NETWORKDAYS() function that find the number of business days between two dates.

有人对MySQL有类似的功能吗?由于假期增加了复杂性，因此该解决方案不必处理假期.

Anybody have a similar function for MySQL? Since holidays adds complexity, the solution doesn't have to deal with holidays.

推荐答案

此表达式-

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400012345001234550', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

计算开始日期@S和结束日期@E之间的工作日数.

calculates the number of business days between the start date @S and the end date @E.

假定结束日期(@E)不早于开始日期(@S). 与DATEDIFF兼容，起始日期和结束日期相同 给出零个工作日. 忽略假期.

Assumes end date (@E) is not before start date (@S). Compatible with DATEDIFF in that the same start date and end date gives zero business days. Ignores holidays.

数字字符串的构造如下.创建一个表 开始日期和结束日期，这些行必须以星期一(WEEKDAY)开始 0)，列也必须以星期一开始.填写 从左上到右下的对角线全为0(即0 周一至周一，周二至周二等的工作日). 对于每一天，从对角线开始(必须始终为0)并填写 右侧的列，一次一天.如果您降落在 周末(非工作日)列，工作日数 不会改变，它是从左侧携带的.否则，数字 个工作日增加一倍.当您到达终点时 行循环返回到同一行的开头，并继续直到 再次到达对角线.然后转到下一行.

The string of digits is constructed as follows. Create a table of start days and end days, the rows must start with monday (WEEKDAY 0) and the columns must start with Monday as well. Fill in the diagonal from top left to bottom right with all 0 (i.e. there are 0 working days between Monday and Monday, Tuesday and Tuesday, etc.). For each day start at the diagonal (must always be 0) and fill in the columns to the right, one day at a time. If you land on a weekend day (non business day) column, the number of business days doesn't change, it is carried from the left. Otherwise, the number of business days increases by one. When you reach the end of the row loop back to the start of the same row and continue until you reach the diagonal again. Then go on to the next row.

例如假设星期六和星期日不是工作日-

E.g. Assuming Saturday and Sunday are not business days -

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

然后将表中的49个值连接到字符串中.

Then concatenate the 49 values in the table into the string.

如果发现任何错误，请告诉我.

Please let me know if you find any bugs.

-编辑 改进的表格:

 | M T W T F S S
-|--------------
M| 0 1 2 3 4 4 4
T| 4 0 1 2 3 3 3
W| 3 4 0 1 2 2 2
T| 2 3 4 0 1 1 1
F| 1 2 3 4 0 0 0
S| 0 1 2 3 4 0 0
S| 0 1 2 3 4 4 0

改进的字符串:'0123444401233334012222340111123400001234000123440'

improved string: '0123444401233334012222340111123400001234000123440'

改进的表达方式:

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

这篇关于MySQL函数查找两个日期之间的工作日数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！