Count Sundays Between Two Dates

Once day I developed one application, the system required to count number of Sundays between duration of date. I tried for 2 hour to find solution to get number of Sundays. And after I finished my work I want to share this tip to other developer. I wrote the scripts to count, we just input parameter @StartDate and @EndDate, it will show result.

Please run this code and test your self. 

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;

-----------------------------------------------------------------------------------------------

Output: Number of sundays between two date is 74 sundays.