Query to find First Sunday of March and Second Sunday of November

 declare @dt datetime='1/1/2014'

;with mycte as (
select 0 as n
select n+1 from mycte WHERE n<20

,mycte2 AS
select CAST (a.n as nvarchar(3)) as n1
, CAST (b.n as nvarchar(3)) as n2 from mycte a
, mycte b)

--Generate a calendar datetime list.
--It is a good practice to work with an auxillary calendar table
,mycalendar as
SELECT Dateadd(day,ROW_NUMBER()OVER(order by CAST(n1+n2 as int))-1, @dt) as dtCol
FROM mycte2 )

,mycte4 as (
Select dtCol, Row_number() Over(Partition by Year(dtCol), Month(dtCol)
, datename(weekday,dtcol) Order By dtCol) rn, datename(weekday,dtcol) dtweekday
FROM mycalendar

Select dtcol,dtweekday from mycte4
AND ((rn=2 and Month(dtcol)=3) Or ( rn=1 AND month(dtcol)=11 ))




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