For the current month ...

WITH MYCTE AS (

SELECT row_number() over (order by [dates] DESC ) as dayNum, [dates], datename(weekday, dates) as [Day Name]

FROM

( /* the last seven days in the month*/

SELECT dateadd(dd, ROW_NUMBER() OVER( ORDER BY (SELECT 'a')) -1, dateadd(m,1,dateadd(d,-day(getdate())+1, dateadd(d, 0, datediff(d, 0 , getdate()))))-7) as [Dates]

from (VALUES(1),(2),(3),(3),(4),(5),(6))L(s)

) as v

WHERE datename(weekday, dates) not in ('saturday','sunday')

)

SELECT * from MYCTE where dayNum = 2

You can substitute getdate() with a datetime or date variable and make it any day in the month you wish to analyse.