title: Calculate all "Sundays,Mondays...Saturdays" between two days in SQL Server
body: <p>I want to calculate total say <code>Sundays,Mondays...Saturdays</code> between two days. I want a do it in a select query because that is mandatory according to the situation where I'm currently working.</p>
  
  <p>I have some working code where I'm able to calculate all sundays but not working in case of mondays</p>
  
  <pre><code>DECLARE  @StartDate DATE = '2011-10-01',
           @EndDate   DATE = '2011-10-31' 
  
  SELECT DayCount = count(* )
  FROM   (SELECT TOP ( datediff(DAY,@StartDate,@EndDate) + 1 )
                          [Date] = dateadd(DAY,ROW_NUMBER()
                    OVER(ORDER BY c1.name),
                    DATEADD(DD,-1,@StartDate))
          FROM   [master].[dbo].[spt_values] c1 ) x
  WHERE  datepart(dw,[Date]) = 1;
  </code></pre>