This one is simple, but I get asked a lot how to get only the workdays within a given range of dates. For my own use, I added holidays to the mix (removing them from the results), and you can as well although for simplicity here I am just showing all days that are not weekends. You can also modify the select statement at the end of the procedure. This example gives a simple count of workdays between two dates. You could of course simply select them all instead of returning just a count of days between the two dates.
Code:
CREATE PROCEDURE sp_GetWorkdays (@StartDate datetime, @EndDate datetime) AS 
DECLARE @DateTable TABLE 
( 
    [day_of_week] int, 
    [date] datetime 
) 
 
DECLARE @DateCounter datetime 
SET @DateCounter = @StartDate 

WHILE @DateCounter <= @EndDate 
BEGIN 
  INSERT INTO @DateTable VALUES (DATEPART(dw, @DateCounter), @DateCounter) 
  SET @DateCounter = DATEADD(day, 1, @DateCounter) 
END 

DELETE FROM @DateTable WHERE day_of_week IN (1, 7) 

SELECT COUNT([date]) FROM @DateTable