title: SQL - Get all dates between Current day and start and end of week
body: <p>I am trying to set the start day to Saturday</p>
  
  <pre><code>SET DATEFIRST 6 -- Sets start day to Saturday
  </code></pre>
  
  <p>And then get all the GameDate between the start of the week and the end of the week. But it needs to be done with the current day. For instance: if the current day is thursday and the end of the week is Friday I don't want to do this:</p>
  
  <pre><code>DECLARE @StartWeek datetime Set @StartWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), -5))
  DECLARE @EndWeek datetime Set @EndWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), 1))
  </code></pre>
  
  <p>Because this will make it that the next 5 days will get shown.
  And If the CurrentDay is on Thursday I don't want the next 5 days to get shown. Just the GameDates from every week.</p>
  
  <p>This is what I got so far:</p>
  
  <pre><code>DECLARE @DateTable Table (DateofWeek Date) -- Creates table
  DECLARE @DateToday Date SELECT DAY(GETDATE()) 'Current Day' -- Gets current date
  
  DECLARE @StartWeek datetime Set @StartWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), -5)) --This is the part thats wrong
  DECLARE @EndWeek datetime Set @EndWeek = DATEADD(w, 0, DATEADD(w, DATEDIFF(w, 0,GETDATE()), 1)) --This is the part thats wrong
  
  SET DATEFIRST 6 -- Sets start day to Saturday
  
  
  SELECT DATEDIFF ( DAY, @DateToday, @EndWeek) AS Diffrence_End
  SELECT DATEDIFF ( DAY, @DateToday, @StartWeek) AS Diffrence_Start
      FROM @DateTable
  
  WHILE @DateToday &gt;= @EndWeek AND @DateToday &lt;= @StartWeek -- Shows all gameDates between StartWeek and Endweek
  BEGIN   
      SELECT DATEDIFF ( DAY, @DateToday, @EndWeek)
      Insert Into @DateTable
  
  
  
  SELECT * 
      FROM @DateTable
  END;
  </code></pre>
  
  <p>Some of the Query is probably wrong, especially the last part.</p>
  
  <p>I hope I made myself clear enough, if there are any questions please don't hesitate to ask me!</p>