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 VIDEOS
 
 
SQL E/BOOKS   WORLD, USA & SQL NEWS   FORMAT
SCRIPTS SQL 2005 SQL 2008 ARTICLES
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
SQL 2016 DESIGN & PROGRAMMING
 
 
 
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