Get Third Mondays in a Month (February) from a Date Range


  
  
  DECLARE  @StartDate  DATETIME = '2012-03-05'
DECLARE @EndDate DATETIME = '2019-02-19'
 

--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
    
  
,DateRange as
(
select  dateadd(day,n-1,@startdate) dt from Nums  
where dateadd(day,n-1,@startdate)<=@EndDate   
)

 SELECT distinct DateAdd(Month, DateDiff(Month, 0, dt), 0)+6+14
             -(DATEPART (Weekday, DateAdd(Month, DateDiff(Month, 0, dt), 0))
             +@@DateFirst+4)%7 --Third Monday IN the month

 from DateRange
 WHERE month(dt)=2 --In February


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/becdad16-c7de-40cf-9858-b7d56fe2714f/get-all-third-mondays-on-a-given-date-range?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s