Execute the following Microsoft SQL Server 2008 T-SQL scripts in Query Editor get a count of Sundays in the given date range.

datepart(dw,[Date]) = 1 indicates Sunday by default for US English language. DATE (date only without time, 3 bytes) data type is new to SQL Server 2008.

-- T-SQL find the count of Sundays in date range

-- Using spt_values to create a sequence (2506 rows)

USE AdventureWorks2008;

DECLARE  @StartDate DATE = '2012-01-01',

         @EndDate   DATE = '2013-06-01' 

SELECT Sundays = count(* )

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

                        [Date] = dateadd(DAY,ROW_NUMBER()

                  OVER(ORDER BY c1.name),

                  DATEADD(DD,-1,@StartDate))

        FROM   [master].[dbo].[spt_values] c1 ) x

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

/*

Sundays

74

*/

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

-- T-SQL count Sunday using dynamically generated sequence

USE AdventureWorks2008;

DECLARE  @StartDate DATE = '2012-01-01',

         @EndDate   DATE = '2013-06-01' 

;WITH cteSequence ( SeqNo) as

(

      SELECT 1  UNION ALL SELECT SeqNo + 1

      FROM cteSequence

      WHERE SeqNo < DATEDIFF(DD, @StartDate, @EndDate)+1

)

SELECT Sundays = count(* )

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

                        [Date] = dateadd(DAY,ROW_NUMBER()

                  OVER(ORDER BY c1.SeqNo),

                  DATEADD(DD,-1,@StartDate))

        FROM   cteSequence c1 ) x

WHERE  datepart(dw,[Date]) = 1

OPTION ( MAXRECURSION 0);

/*

Sundays

74

*/

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

Related link: http://sqlusa.com/bestpractices/datetimeconversion/