Posted: February 27, 2019 | Author: Jingyang Li | Filed under: Uncategorized |
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
Jingyang Li 