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
UNION ALL
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
WHERE
dtweekday='Sunday'
AND ((rn=2 and Month(dtcol)=3) Or ( rn=1 AND month(dtcol)=11 ))

&nbsp;

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/442bf0d8-d38e-4c34-b024-4e31509dd235/query-to-find-first-sunday-of-march-and-second-sunday-of-november-in-sql-2008?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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s