Tuesday, 26 August 2014

SQL server: Get the date of the first Monday in September

 
DECLARE @FirstOfSeptember DATETIME
DECLARE @FirstMonday DATETIME
 
 SET @FirstOfSeptember ='2009-09-01'   ---First 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

No comments: