Return number of weekdays between 2 dates in T-SQL

I have 2 dates and want to know how many weekdays (mon-fri) there are

e.g.

thu jan 1 20xx    
fri jan 2 20xx    
sat jan 3 20xx    
sun jan 4 20xx 开发者_运维知识库   
mon jan 5 20xx  

jan 1, jan 5 would return 3

(can ignore public holidays)

Try

 DateDiff(day, @DtA, @DtB) - 2 * DateDiff(Week, @DtA, @DtB)

this may not work exactly, but you can see the idea. Some slight modification will work.

Assuming the dates can't be more than five and a half years from each other (or use your own tally table instead of master..spt_values):

DECLARE @date1 datetime, @date2 datetime;
SET @date1 = '20110901';
SET @date2 = '20110905';

SELECT COUNT(*)
FROM (
  SELECT
    Date = DATEADD(day, number, @date1)
  FROM master..spt_values
  WHERE type = 'P'
    AND number between 0 AND DATEDIFF(day, @date1, @date2)
) s
WHERE DATENAME(DW, Date) NOT IN ('Saturday', 'Sunday')

try this:

SET DATEFIRST 1
DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate='6/21/2011'
      ,@EndDate='6/28/2011'
;with AllDates AS
(
    SELECT @StartDate AS DateOf, datepart(weekday,getdate()) AS WeekDayNumber
    UNION ALL
    SELECT DateOf+1, datepart(weekday,DateOf+1)
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT COUNT(*) AS WeekDayCount FROM AllDates WHERE WeekDayNumber<=5

OUTPUT:

WeekDayCount
------------
6

(1 row(s) affected)

If you have a holiday table, you can join it in and remove those as well.

EDIT based on @Ross Watson comment:

SET DATEFIRST 1
DECLARE @StartDate datetime
       ,@EndDate datetime
SELECT @StartDate='6/21/2011'
      ,@EndDate='6/28/2011'
;with AllDates AS
(
    SELECT @StartDate AS DateOf, datepart(weekday,getdate()) AS WeekDayNumber
    UNION ALL
    SELECT DateOf+1, (WeekDayNumber+1) % 7
        FROM AllDates
    WHERE DateOf<@EndDate
)
SELECT COUNT(*) AS WeekDayCount FROM AllDates WHERE WeekDayNumber>0 AND WeekDayNumber<6
--I don't like using "BETWEEN", ">", ">=", "<", and "<=" are more explicit in defining end points

produces same output as original query.

Try the following:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2011/06/01'
SET @EndDate = '2011/06/31'

SELECT   
    (DATEDIFF(dd, @StartDate, @EndDate) + 1)  - 
    (DATEDIFF(wk, @StartDate, @EndDate) * 5)  -
    (
        CASE 
           WHEN DATENAME(dw, @StartDate) in 
               ('Sunday', 'Tuesday', 'Wednesday', 'Turesday', 'Saturday') 
           THEN 1 
           ELSE 0 
        END
    )  -
    (
        CASE 
           WHEN DATENAME(dw, @EndDate) in 
               ('Sunday', 'Tuesday', 'Wednesday', 'Turesday', 'Saturday') 
           THEN 1 
           ELSE 0 
        END
    )

Also see if this is helpful http://blog.sqlauthority.com/2007/06/08/sql-server-udf-function-to-display-current-week-date-and-day-weekly-calendar/

This approach is limited to ~100 days due to recursion. This works for the date ranges i've tested. Same idea above, removed the math and simplified:

BEGIN
    SET DATEFIRST 1
    DECLARE @StartDate datetime
           ,@EndDate datetime
    SELECT @StartDate='12/16/2015'
          ,@EndDate='1/8/2016'
    ;with AllDates AS
    (
        SELECT @StartDate AS DateOf 
        UNION ALL
        SELECT DateOf+1 
            FROM AllDates
        WHERE DateOf<@EndDate
    )
    SELECT COUNT(*) AS WeekDayCount 
        FROM 
        AllDates 
    WHERE 
        datepart(weekday,DateOf) between 1 AND 5

    --SELECT DateOf [date], datepart(weekday,DateOf) [day] 
    --FROM 
    --  AllDates 
    --WHERE 
    --  datepart(weekday,DateOf) between 1 AND 5
END