• You can use a recursive common table expression to achieve this.

    DECLARE @startDate DATETIME = '8/1/2014'

    DECLARE @endDate DATETIME = '8/31/2014'

    DECLARE @dayOfWeek INT = 1 -- 1=Sun, 7=Sat

    ;WITH cte_Recursion AS

    (

    SELECT @startDate AS [Date]

    UNION ALL

    SELECT DATEADD(DAY, 1, [Date])

    FROM cte_Recursion

    WHERE [Date] < @endDate

    )

    SELECT [Date]

    FROM cte_Recursion

    WHERE DATEPART(WEEKDAY, [Date]) = @dayOfWeek

    OPTION (MAXRECURSION 0) -- MaxRecursion 0 is needed when dates are more than 100 days apart