Find first and last sunday
This is a nifty query that I can use on reporting, I’m sure there are other uses for it.
[sql]
declare @year int
set @year =2011
— First and Last Sunday by SqlServerCurry.com
select min(dates) as first_sunday,max(dates) as last_sunday from
(
select dateadd(day,number-1,DATEADD(year,@year-1900,0))
as dates from master..spt_values
where type=’p’ and number between 1 and
DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0))
) as t
where DATENAME(weekday,dates)=’sunday’
group by DATEADD(month,datediff(month,0,dates),0)
[/sql]
Source: http://www.sqlservercurry.com/2011/02/sql-server-first-and-last-sunday-of.html