SET DATEFIRST 1; -- first day of the week is a Monday
DECLARE @monthINT = 10;
DECLARE @yearINT = 2013;
-- Tally table of 7 rows
WITH CTE_Tally AS
(
SELECT 0 AS Number
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
)
SELECT resultdate = dates
FROM
(
SELECT dates, RID = ROW_NUMBER() OVER (ORDER BY dates)
FROM
(
SELECT dates = DATEADD(dd,Number,DATEFROMPARTS(@year,@month,1))
FROM CTE_Tally
) tmp
WHERE DATEPART(weekday,dates) NOT IN (6,7) -- filter out saturday and sunday
) tmp2
WHERE RID = 5;
I used the function DATEFROMPARTS, which is only available from SQL Server 2012. Since you posted this question in a SQL 2014 forum, I assume that won't be a problem.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP