Simple query to get weekdays between two date fields

Sometimes you have two date fields in a table and you want to return the number of week days between them (i.e., the number of days excluding the weekend).

On the web, you will find several ways to program stored functions in code blocks (in PL/SQL or SQL Server or whatever your favorite flavor is) that will compute this function. But this isn't necessary.

You can use a simple expression in your SQL statement that will return the number of week days between two date fields.

The general strategy is to construct an expression that does the following:

• Take the raw difference in days
• Subtract 2 days for every full 7 days that has passed
• If the second date falls on an earlier day of the week than the first date, add another two days for the most recent weekend that has passed
• Correct the final total for anomalies when one or the other day actually falls on a weekend

Using Oracle SQL:

SELECT
    date1,
    date2,
    (date2-date1)-2*FLOOR((date2-date1)/7)-DECODE(SIGN(TO_CHAR(date2,'D')-
        TO_CHAR(date1,'D')),-1,2,0)+DECODE(TO_CHAR(date1,'D'),7,1,0)-
        DECODE(TO_CHAR(date2,'D'),7,1,0) as WorkDays
  FROM
    datetest
  ORDER BY date1,date2

(I know that date formatting and certain functions like DECODE are different for MySQL and SQL Server. Can someone provide the equivalent of this expression using MySQL or SQL Server syntax? Put it in a comment and I will add it here, or you can just post it as an alternate. In theory, the logic of this expression could be reproduced in any flavor of SQL.)

This function will return the number of days between date1 and date2, excluding weekends. Here is a result of this query using a table with some sample data:

DATE1          DATE2          WORKDAYS
7/1/2011       7/20/2011      13
7/2/2011       7/20/2011      13
7/3/2011       7/20/2011      13
7/4/2011       7/4/2011       0
7/4/2011       7/5/2011       1
7/4/2011       7/6/2011       2
7/4/2011       7/7/2011       3
7/4/2011       7/8/2011       4
7/4/2011       7/9/2011       4
7/4/2011       7/10/2011      4
7/4/2011       7/11/2011      5
7/4/2011       7/12/2011      6
7/4/2011       7/13/2011      7
7/4/2011       7/14/2011      8
7/4/2011       7/15/2011      9
7/4/2011       7/16/2011      9
7/4/2011       7/17/2011      9
7/4/2011       7/18/2011      10
7/4/2011       7/19/2011      11
7/4/2011       7/20/2011      12