SQL Server: Query to Get the Date of First Monday of September

Introduction

Below is a query to get the date of the first Monday in Sept (Labour day in Canada). It can be easily modified to get the date of the first Monday (or any other day) of any other month of the year.

Using the Code

In this tip, we suppose that Sunday=1 (the first day of the week).

DECLARE @FirstOfSeptember DATETIME 
DECLARE @FirstMonday DATETIME 

SET @FirstOfSeptember ='2009-09-01' ---1sth of sept 2009 
SELECT @FirstMonday = CASE 
WHEN DATEPART(WEEKDAY, DATEADD(DAY, 0,@FirstOfSeptember )) = 2 THEN @FirstOfSeptember 
WHEN DATEPART(WEEKDAY, DATEADD(DAY, 1,@FirstOfSeptember )) = 2 THEN DATEADD(DAY,1,@FirstOfSeptember ) 
WHEN DATEPART(WEEKDAY, DATEADD(DAY, 2,@FirstOfSeptember )) = 2 THEN DATEADD(DAY,2,@FirstOfSeptember ) 
WHEN DATEPART(WEEKDAY, DATEADD(DAY, 3,@FirstOfSeptember )) = 2 THEN DATEADD(DAY,3,@FirstOfSeptember ) 
WHEN DATEPART(WEEKDAY, DATEADD(DAY, 4,@FirstOfSeptember )) = 2 THEN DATEADD(DAY,4,@FirstOfSeptember ) 
WHEN DATEPART(WEEKDAY, DATEADD(DAY, 5,@FirstOfSeptember )) = 2 THEN DATEADD(DAY,5,@FirstOfSeptember ) 
WHEN DATEPART(WEEKDAY, DATEADD(DAY, 6,@FirstOfSeptember )) = 2 THEN DATEADD(DAY,6,@FirstOfSeptember ) END

History

This is my first post on CodeProject.