MySQL function to find the number of working days between two dates

Question

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

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

Answer 1

This expression -

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

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

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.

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 

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

Please let me know if you find any bugs.

-Edit improved table:

 | 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 

improved string: '0123444401233334012222340111123400001234000123440'

improved expression:

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

Answer 2

This solution uses basically the same approach as Rodger's except the method for generating the matrix is much more complex. Note: This output of this solution is not compatible with NETWORKDAYS.

As in Rodger's solution, this calculates the number of business days between the start date (@S) and the end date (@E) without having to define a stored procedure. It assumes that the end date is not before the start date. Using the same start and end date will produce 0. Holidays are not taken into account.

The major difference between this and Rodger's solution is that the matrix and resulting string of digits is constructed by a complex algorithm which I have not included. The output of this algorithm is validated by a unit test (see the test inputs and outputs below). In the matrix, the intersection of any given x and y value pair (WEEKDAY(@S) and WEEKDAY(@E) yields the difference in work days between the two values. The assignment order is actually unimportant as the two are added together to plot the position.

Business days are Monday-Friday

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

The 49 values in the table are concatenated into the following string:

0123455501234445012333450122234501101234000123450 

In the end, the correct expression is:

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

I have verified the following inputs and outputs using this solution:

Sunday, 2012-08-26 -> Monday, 2012-08-27 = 0 Sunday, 2012-08-26 -> Sunday, 2012-09-02 = 5 Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1 Monday, 2012-08-27 -> Monday, 2012-09-10 = 10 Monday, 2012-08-27 -> Monday, 2012-09-17 = 15 Monday, 2012-08-27 -> Tuesday, 2012-09-18 = 16 Monday, 2012-08-27 -> Monday, 2012-09-24 = 20 Monday, 2012-08-27 -> Monday, 2012-10-01 = 25 Tuesday, 2012-08-28 -> Wednesday, 2012-08-29 = 1 Wednesday, 2012-08-29 -> Thursday, 2012-08-30 = 1 Thursday, 2012-08-30 -> Friday, 2012-08-31 = 1 Friday, 2012-08-31 -> Saturday, 2012-09-01 = 1 Saturday, 2012-09-01 -> Sunday, 2012-09-02 = 0 Sunday, 2012-09-02 -> Monday, 2012-09-03 = 0 Monday, 2012-09-03 -> Tuesday, 2012-09-04 = 1 Tuesday, 2012-09-04 -> Wednesday, 2012-09-05 = 1 Wednesday, 2012-09-05 -> Thursday, 2012-09-06 = 1 Thursday, 2012-09-06 -> Friday, 2012-09-07 = 1 Friday, 2012-09-07 -> Saturday, 2012-09-08 = 1 Saturday, 2012-09-08 -> Sunday, 2012-09-09 = 0 Monday, 2012-09-24 -> Sunday, 2012-10-07 = 10 Saturday, 2012-08-25 -> Saturday, 2012-08-25 = 0 Saturday, 2012-08-25 -> Sunday, 2012-08-26 = 0 Saturday, 2012-08-25 -> Monday, 2012-08-27 = 0 Saturday, 2012-08-25 -> Tuesday, 2012-08-28 = 1 Saturday, 2012-08-25 -> Wednesday, 2012-08-29 = 2 Saturday, 2012-08-25 -> Thursday, 2012-08-30 = 3 Saturday, 2012-08-25 -> Friday, 2012-08-31 = 4 Saturday, 2012-08-25 -> Sunday, 2012-09-02 = 0 Monday, 2012-08-27 -> Monday, 2012-08-27 = 0 Monday, 2012-08-27 -> Tuesday, 2012-08-28 = 1 Monday, 2012-08-27 -> Wednesday, 2012-08-29 = 2 Monday, 2012-08-27 -> Thursday, 2012-08-30 = 3 Monday, 2012-08-27 -> Friday, 2012-08-31 = 4 Monday, 2012-08-27 -> Saturday, 2012-09-01 = 5 Monday, 2012-08-27 -> Sunday, 2012-09-02 = 5