FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to count the days in a month excluding weekends?

Execute the following SQL Server T-SQL example script in Management Studio Query Editor to create a user-defined function for workday in a month calculations. To include holidays in the calculation would require a Holiday table.

-- Working days in month - Days in month excluding Sundays

-- SQL Server user-defined scalar function

USE AdventureWorks;

GO

CREATE FUNCTION fnDaysInMonthExcludingWeekends

               (@Year  INT,

                @Month INT)

RETURNS INT

AS

  BEGIN

    DECLARE  @FirstDay SMALLDATETIME,

             @LastDay  SMALLDATETIME,

             @Days     INT

    DECLARE  @RunningDay SMALLDATETIME

    SET @FirstDay = convert(SMALLDATETIME,convert(VARCHAR,@Year) + '/'

                  + convert(VARCHAR,@Month) + '/01')

    SET @LastDay = dateadd(DAY,-1,dateadd(MONTH,1,@FirstDay))

    SET @Days = 0

    SET @RunningDay = @FirstDay

    WHILE (@RunningDay <= @LastDay)

      BEGIN

        IF datepart(dw,@RunningDay) not in (1, 7) -- Exclude SUN, SAT

          SET @Days = @Days + 1

        SET @RunningDay = @RunningDay + 1

      END

    RETURN (@Days)

  END

GO

 

-- Test user-defined function

SELECT dbo.fnDaysInMonthExcludingWeekends(2005,12)

GO

-- 22

 

SELECT dbo.fnDaysInMonthExcludingWeekends(2012, 2)

GO

-- 21

 

Exam Prep 70-461
Exam 70-461