Expanding on Jacks solution, but eliminating the need to "hope" for @@DateFirst being either 1 or 0, here's an approach that's totally independent on the setting of @@DateFirst.
the biggest difference is the way to calculate DayNo:
DATEDIFF(dd,-1,theDate)%7 AS DayNo,
This code snippet is based on the fact that "day Zero" = 1900-01-01, which is a Monday.
The Modulo 7 will return the number of weekdays between 1900-01-01 an theDate, Zero, if theDate is a Monday and 6 if theDate is Sunday.
By shifting the day using -1 it'll start with Zero for Sunday to 6 for Saturday.
This code is also independent of any language setting (whereas Ed's solution for instance will fail if there's a SET LANGUAGE "GERMAN" is involved before his code).
@jack-2 and Ed: I'm sorry, but I'm a strong advocate against any form of non-deterministic date functions that'll rely on DATEFIRST and/or LANGUAGE settings. At least as long as there are alternatives available 😉
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 has
the 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;