DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
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
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE