title: How to get all the weekend dates of the current year in SQL?
body: <p>I tried but could not get the right solution. I want an SQL query that lists all the weekend dates of the current year.</p>
  
  <p>I tried this SQL query:</p>
  
  <pre><code>WITH hier(num, lvl) AS (
      SELECT  0, 1
              UNION ALL
      SELECT  100, 1
              UNION ALL 
      SELECT  num + 1, lvl + 1
      FROM    hier
      WHERE   lvl &lt; 100 
  )
  SELECT lvl [Week], 
  convert(date,DATEADD(dw, -DATEPART(dw, DATEADD(wk,DATEDIFF(wk,0,'12/31/'+convert(nvarchar,YEAR(getdate()))), 0)+6 ),
  DATEADD(wk, DATEDIFF(wk,0,'12/31/'+convert(nvarchar,YEAR(getdate()))), 0)+6 ) - num  * 7,101) [End Date]
  FROM    hier a
  where   num &lt; 52
  ORDER BY [End Date] asc
  </code></pre>
  
  <p>Its output is like this:</p>
  
  <pre><code>Week  End date
  52  2012-01-14
  51  2012-01-21
  50  2012-01-28
  49  2012-02-04
  </code></pre>
  
  <p>I want the dates to start from the beginning – so, the above is missing one weekend, which is <code>2012-07-01</code>. Also, I want the week numbers to show as <code>1, 2, 3...</code> instead of <code>52, 51...</code>.</p>