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

Mohamad Cheaito

Rate me:

4.50/5 (2 votes)

12 Sep 2014CPOL

26.7K

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).

SQL

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

Mohamad Cheaito

Canada

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

SET @FirstMonday = DATEADD ( dd , ( 9 - DATEPART ( dw , @FirstOfSeptember ) ) % 7 , @FirstOfSeptember ) -------------------333333333333331111111000000000000000000000000000000000001122222333333333333333333333

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

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

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

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

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

Introduction

Using the Code

History

License

Comments and Discussions