How Can I Calculate Business Days Between Two Dates in MySQL Without Considering Holidays?

Efficiently Calculating Business Days (Excluding Weekends) in MySQL

Many applications require calculating the number of business days between two dates. While spreadsheet software often provides built-in functions for this (like NETWORKDAYS), MySQL lacks a direct equivalent. This article presents a concise MySQL expression to accomplish this, focusing on the exclusion of weekends. Note that this solution does not account for holidays.

The MySQL Expression

To determine business days between dates @S (start date) and @E (end date), use the following:

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

How it Works:

This expression cleverly combines several MySQL functions:

1. DATEDIFF(@E, @S): Calculates the total number of days between the two dates.

2. DIV 7: Divides the total days by 7, providing the number of full weeks.

3. *` 5`:** Multiplies the number of weeks by 5 to account for the five business days in each week.

4. WEEKDAY(@S) and WEEKDAY(@E): Determine the day of the week (0 for Sunday, 1 for Monday, etc.) for both the start and end dates.

5. *`7 WEEKDAY(@S) WEEKDAY(@E) 1`:** This calculation generates an index into the lookup string.

6. MID('0123444401233334012222340111123400012345001234550', ..., 1): The MID function uses the calculated index to extract a single digit from a cleverly constructed string. This digit represents the remaining business days that aren't captured by the full weeks calculation (accounting for partial weeks at the beginning and end).

This method offers a fast and efficient way to approximate business days in MySQL, excluding weekends, without the complexity of handling holidays. For a more comprehensive solution including holidays, a more elaborate approach involving a holiday table would be necessary.

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