USE AdventureWorks

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = '2009-01-01'

SET @EndDate = '2010-06-01'

SELECT Sundays=COUNT(*)

FROM (SELECT TOP (DATEDIFF (DAY, @StartDate, @EndDate) +1)

[Date] = DATEADD(DAY, ROW_NUMBER()

OVER(ORDER BY c1.name, c2.name), CONVERT(char(10),@StartDate-1,110))

FROM sys.columns c1

CROSS JOIN sys.columns c2) x

WHERE DATEPART(dw,[Date]) = 1;

Result:

Sundays
74

Continue...................