kherald provided a solution. Here's my take which still lets you use 0 for Sunday instead of 1 and also takes into account the Language setting of your SQL Server because some languages (actually most in the version of SQL Server I have installed) use Monday as day 1 not Sunday. You can combine both solutions to make it fit your purposes:

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 has

the 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;