I have often seen questions about finding the First Sunday or Monday of every month in SQL Server. Here’s a query that lists the first Sunday of every month in a given duration
Note: You can also check List all the Weekends of the Current Year using SQL Server and First weekday of a month in SQL Server
DECLARE @dow int,
SELECT @dow = 1, -- Sunday
@StartDate = '01 Jan 2010',
@EndDate = '31 Dec 2011'
-- Populate CTE with the 1st date of every month
;WITH CTE(mth) AS (
SELECT @StartDate mth UNION ALL
SELECT DATEADD(month,1,mth) FROM CTE
WHERE DATEADD(month,1,mth) <= @EndDate
-- SQLServerCurry.com Query to List First Sunday of all Months
SELECT DATEADD(DAY, @dow +
CASE WHEN DATEPART(dw,mth) > @dow THEN 7
- DATEPART(dw, mth), mth) as 'First Sunday'
Update: Here's a better query SQL Server: First and Last Sunday of Each Month