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

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

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.

Solution

Illustration:

mtwtfSSmtwtfSS
  123456712345   one week plus 5 days, you can remove whole weeks safely
  12345-------   you can analyze partial week's days at start date
  -------12345   or at ( end date - partial days )

Pseudocode:

@S          = start date
@E          = end date, not inclusive
@full_weeks = floor( ( @E-@S ) / 7)
@days       = (@E-@S) - @full_weeks*7   OR (@E-@S) % 7

SELECT
  @full_weeks*5 -- not saturday+sunday
 +IF( @days >= 1 AND weekday( S+0 )<=4, 1, 0 )
 +IF( @days >= 2 AND weekday( S+1 )<=4, 1, 0 )
 +IF( @days >= 3 AND weekday( S+2 )<=4, 1, 0 )
 +IF( @days >= 4 AND weekday( S+3 )<=4, 1, 0 )
 +IF( @days >= 5 AND weekday( S+4 )<=4, 1, 0 )
 +IF( @days >= 6 AND weekday( S+5 )<=4, 1, 0 )
 -- days always less than 7 days