Count days between two dates, excluding weekends (MySQL only)

Question

I need to calculate the difference (in days) between two dates in MySQL excluding weekends (Saturday and Sunday). That is, the difference in days minus the number of Saturday and Sunday in between.

At the moment, I simply count the days using:

SELECT DATEDIFF('2012-03-18', '2012-03-01')

This return 17, but I want to exclude weekends, so I want 12 (because the 3rd and 4th, 10th and 11th and 17th are weekends days).

I do not know where to start. I know about the WEEKDAY() function and all related ones, but I do not know how to use them in this context.

Answer 1

Simply try it using a simple function :

CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE)
RETURNS INT
RETURN ABS(DATEDIFF(date2, date1)) + 1
     - ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 - DAYOFWEEK(date2) DAY),
                    ADDDATE(date1, INTERVAL 1 - DAYOFWEEK(date1) DAY))) / 7 * 2
     - (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1)
     - (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7);

Test :

SELECT TOTAL_WEEKDAYS('2013-08-03', '2013-08-21') weekdays1,
       TOTAL_WEEKDAYS('2013-08-21', '2013-08-03') weekdays2;

Result :

| WEEKDAYS1 | WEEKDAYS2 |
-------------------------
|        13 |        13 |