title: Last Sunday to last Saturday date calculation (not in where statement, but select) SQL
body: <p>I see a lot of examples how to calculate dates from last Sunday to last Saturday in a where statement, but I didn't find any for the last week in SELECT statement.</p>
  <p>if I need to have a formula [U_ONSALE_DATE] between last Sunday (03/06) and last Saturday (03/12) so that it dynamically changes each week (Sun - Sat).</p>
  <p>I was trying to do it this way, but it didn't work as expected.</p>
  <pre><code>[U_ONSALE_DATE] BETWEEN  DATEDIFF(day,getdate(),f.[STARTDATE]) = -1 AND DATEDIFF(day,getdate(),f.[STARTDATE]) = -6 
  </code></pre>
  <p>I have such a visual. Titles On Sale Last Week.</p>
  <p><a href="https://i.stack.imgur.com/4dUNU.jpg" rel="nofollow noreferrer">enter image description here</a></p>
  <p>The way how I was calculating the dates before in a SELECT statement was with the CASE statement.</p>
  <pre><code>,CASE WHEN DATEADD(DAY,0,DATEDIFF(DAY,0,[U_ONSALE_DATE])) 
  &lt;= DATEADD(DAY,-7,DATEDIFF(DAY,0,GETDATE())) 
  AND 
  DATEADD(DAY,0,DATEDIFF(DAY,0,[U_ONSALE_DATE])) 
  &gt; DATEADD(DAY,-14,DATEDIFF(DAY,0,GETDATE()))
  THEN 'Last Week On Sale Date'
  ELSE NULL END AS 'Last Week On Sale Date'
  </code></pre>
  <p>However, this case statement basically takes titles from -7 (one week ago) to - 14 (2 weeks ago). But this is not actually what I need, I need this case statement to take [U_ONSALE_DATE] and take dates from Sunday to Saturday of the last week and it obviously will be changing each week. like today last Sun to Sat is 03/06 to 03/12.</p>