This post shows you how to find the First and Last Sunday for each month of a given year, in SQL Server. Let us see the t-sql code first
Here’s the same code for you to try out:
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
as dates from master..spt_values
where type='p' and number between 1 and
) as t
group by DATEADD(month,datediff(month,0,dates),0)
For this example, we are using the table master..spt_values which is a system table and contains a number column that has numbers from 0 to 2047. We can make this as a number table. Alternatively you can also create a large number table and use it in the query.
Based on the the year value given (in our case 2011), the first day of that year should be calculated. The code DATEADD(year,@year-1900,0) will give us the First day of the year @year.
We need to consider all the date values from January 1st of that year to December 31st.
The where condition number between 1 and DATEDIFF(day,DATEADD(year,@year-1900,0),DATEADD(year,@year-1900+1,0)) makes sure that all date values for that year are considered.
We then find whether the date is a Sunday by using DATENAME function. As we need the first and last Sunday for each month, we need to group the result by each month. The code DATEADD(month,datediff(month,0,dates),0) used in group by clause, groups the
result by each month.