How Can I Efficiently Calculate Business Days Between Two Dates in MySQL Without Using a Holiday Table?

MySQL working day counting function

Excel’s NETWORKDAYS() function can efficiently calculate the number of working days between two specified dates. In MySQL, similar functions are often needed, especially one that excludes holidays for simplicity.

One way is to use the following expression:

<code class="language-sql">5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)</code>

To use this expression effectively:

1. Make sure the end date (@E) is no earlier than the start date (@S).
2. This expression works like DATEDIFF, if the start date and end date are the same, the number of working days is zero.
3. Please note that this method does not take holidays into account.

The provided numeric string is constructed by creating a table of start and end dates, starting with Monday, arranged in rows and columns. The table is filled diagonally with zeros, indicating that there are no working days between the same day type (for example, Monday to Monday).

For each day, the number of working days is calculated by moving diagonally along the rows to the right. If a non-working day (weekend) is encountered, the number does not change. Otherwise, it is incremented by 1. After reaching the end of the line, the counter resets and the process continues until the diagonal is reached again.

For example, assuming Saturday and Sunday are non-working 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	0	1	2	3	4	0	0
S	0	1	2	3	4	4	0

Concatenating the 49 values ​​in this table produces the string used in the expression.

This solution does not take into account holidays, but it can efficiently count the number of working days between two dates in MySQL.

The above is the detailed content of How Can I Efficiently Calculate Business Days Between Two Dates in MySQL Without Using a Holiday Table?. For more information, please follow other related articles on the PHP Chinese website!