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_RecursionWHERE DATEPART(WEEKDAY, [Date]) = @dayOfWeekOPTION (MAXRECURSION 0) -- MaxRecursion 0 is needed when dates are more than 100 days apart

DECLARE @StartDate DATE = '2014-08-01', @EndDate DATE = '2014-08-31', @DayNo TINYINT = 6;/* this is a virtual numbers/tally table that is used to get all the days between the days. If you already have a calendar table or a table that hasthe dates you are querying this isn't necessary */ WITH nums AS ( SELECT ROW_NUMBER() OVER (ORDER BY ( SELECT NULL )) - 1 AS N FROM sys.all_columns AS AC ), Calendar AS ( SELECT CONVERT(DATE, DATEADD(DAY, n, @StartDate)) AS theDate FROM nums ), WeekDays AS ( SELECT *, /* figure what day is the first day of the week. This setting is controlled by the language of the SQL Server and you can see the values for DateFirst in sys.syslanguages */ CASE @@DateFirst /* First day of week is monday (1) and last day of week is Sunday (7)*/ WHEN 1 THEN CASE DATEPART(WEEKDAY, theDate) WHEN 7 THEN 0 ELSE DATEPART(WEEKDAY, theDate) END /* 1 and 7 are the only options for @@DATEFIRST currently so Sunday is first day of week when @@DATEFIRS isn't 1 */ ELSE DATEPART(WEEKDAY, theDate) - 1 END AS DayNo, DATENAME(WEEKDAY, theDate) AS DayName FROM Calendar ) SELECT * FROM WeekDays WHERE WeekDays.theDate BETWEEN @StartDate AND @EndDate AND WeekDays.DayNo = @DayNo;

SET STATISTICS IO ON;DECLARE @StartDate DATE = '2014-08-01', @EndDate DATE = '2014-08-31', @DayNo TINYINT = 6;/* this is a virtual numbers/tally table that is used to get all the days between the days. If you already have a calendar table or a table that hasthe dates you are querying this isn't necessary */WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), -- 1*10^1 or 10 rows E2(N) AS ( SELECT 1 FROM E1 a, E1 b ), -- 1*10^2 or 100 rows E4(N) AS ( SELECT 1 FROM E2 a, E2 b ), -- 1*10^4 or 10,000 rows E8(N) AS ( SELECT 1 FROM E4 a, E4 b ), -- 1*10^8 or 100,000,000 rows nums AS ( SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY ( SELECT NULL )) - 1 AS N FROM E8 ), Calendar AS ( SELECT CONVERT(DATE, DATEADD(DAY, n, @StartDate)) AS theDate FROM nums ), WeekDays AS ( SELECT *, /* figure what day is the first day of the week. This setting is controlled by the language of the SQL Server and you can see the values for DateFirst in sys.syslanguages */ CASE @@DateFirst /* First day of week is monday (1) and last day of week is Sunday (7)*/ WHEN 1 THEN CASE DATEPART(WEEKDAY, theDate) WHEN 7 THEN 0 ELSE DATEPART(WEEKDAY, theDate) END /* 1 and 7 are the only options for @@DATEFIRST currently so Sunday is first day of week when @@DATEFIRS isn't 1 */ ELSE DATEPART(WEEKDAY, theDate) - 1 END AS DayNo, DATENAME(WEEKDAY, theDate) AS DayName FROM Calendar ) SELECT * FROM WeekDays WHERE WeekDays.theDate BETWEEN @StartDate AND @EndDate AND WeekDays.DayNo = @DayNo;GODECLARE @StartDate DATE = '2014-08-01', @EndDate DATE = '2014-08-31', @DayNo TINYINT = 6;;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]) = @DayNoOPTION (MAXRECURSION 0) -- SET STATISTICS IO OFF;

select Sunday = [Date]from F_TABLE_DATE('08/01/2014','08/31/2014') where -- Select Sunday ISO_DAY_OF_WEEK = 7select Monday = [Date]from F_TABLE_DATE('08/01/2014','08/31/2014') where -- Select Monday ISO_DAY_OF_WEEK = 1

Sunday-----------------------2014-08-03 00:00:00.0002014-08-10 00:00:00.0002014-08-17 00:00:00.0002014-08-24 00:00:00.0002014-08-31 00:00:00.000Monday-----------------------2014-08-04 00:00:00.0002014-08-11 00:00:00.0002014-08-18 00:00:00.0002014-08-25 00:00:00.000

declare @dtmStart datetime = '06/01/2014', @dtmEnd datetime = '09/01/2014';with dates_in_range(date_date, date_name) as ( select DATEADD(day, t.N - 1, @dtmStart), datename(weekday, DATEADD(day, t.N - 1, @dtmStart)) from dbo.Tally t where t.N < DATEDIFF(day, @dtmStart, @dtmEnd) + 1)select date_date, date_name from dates_in_range where date_name = 'Wednesday';

DECLARE @StartDate DATE = '20140801', @EndDate DATE = '20140831', @DayNo TINYINT = 0;/* this is a virtual numbers/tally table that is used to get all the days between the days. If you already have a calendar table or a table that hasthe dates you are querying this isn't necessary */WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), -- 1*10^1 or 10 rows E2(N) AS ( SELECT 1 FROM E1 a, E1 b ), -- 1*10^2 or 100 rows E4(N) AS ( SELECT 1 FROM E2 a, E2 b ), -- 1*10^4 or 10,000 rows E8(N) AS ( SELECT 1 FROM E4 a, E4 b ), -- 1*10^8 or 100,000,000 rows nums AS ( SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY ( SELECT NULL )) - 1 AS N FROM E8 ), Calendar AS ( SELECT CONVERT(DATE, DATEADD(DAY, n, @StartDate)) AS theDate FROM nums ), WeekDays AS ( SELECT *, DATEDIFF(dd,-1,theDate)%7 AS DayNo, DATENAME(WEEKDAY, theDate) AS DayName FROM Calendar ) SELECT * FROM WeekDays WHERE WeekDays.theDate BETWEEN @StartDate AND @EndDate AND WeekDays.DayNo = @DayNo;