Find All Sundays between two dates in SQL server Using CTE

• A Common table Experession (CTE) can be treated as a temporary result set within the scope of a single SELECT,INSERT,UPDATE or DELTE Statement.
• A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.
• CTE can be self-referencing and can be referenced multiple times in the same query.

 

declare @DateFrom DateTime ='2011-07-01',

            @DateTo DateTime = '2011-07-31'

;WITH CTE(dt)

AS

(

      Select @DateFrom

      Union All

      Select DATEADD(d,1,dt)FROM CTE

      Where dt<@DateTo

)

select 'Sunday',dt from CTE

where DATENAME(dw,dt)In('Sunday')

Output