Get The First And Last Working Day Of Each Month For the Given Year

This store procedure will help to get the first and last working day of each month in a given year.

DECLARE @Year INT
SET @Year=2011

DECLARE @FirstDate DateTime,@LastDate DATETIME, @DayofWeek INT
DECLARE @FirstWorkingDay DATETIME, @LastWorkingDay DATETIME

SET @FirstDate = '01-01-'+CONVERT(VARCHAR(4),@Year)
SET DATEFIRST 1

WHILE (MONTH(@FirstDate) <=12 AND YEAR(@FirstDate)=@Year)
BEGIN
SET @DayofWeek = DATEPART(weekday,@FirstDate)
--First working day of the month
IF(@DayofWeek = 7)
SET @FirstWorkingDay= @FirstDate+1
ELSE IF (@DayofWeek = 6)
SET @FirstWorkingDay = @FirstDate+2
ELSE
SET @FirstWorkingDay = @FirstDate

--Get Last working day of the month
SET @LastDate=DATEADD(mm,1,@FirstDate)-1
SET @DayofWeek = DATEPART(weekday,@LastDate)
IF(@DayofWeek = 7)
SET @LastWorkingDay = @LastDate-2
ELSE IF (@DayofWeek = 6)
SET @LastWorkingDay = @LastDate-1
ELSE
SET @LastWorkingDay = @LastDate

SELECT @FirstWorkingDay AS FirstDay, @LastWorkingDay AS 'Last Day'
SET @FirstDate=DATEADD(mm,1,@FirstDate)
END