title: Need list of weeknumbers between two dates
body: <p>I need a resultset of weeknumers, year and startdate of all weeks between two dates. I need it to match other search results to the weeks. Since the report will span just over a year, I need it to match the calendars.</p>
  
  <p>We're in Europe, so weeks start on MONDAY.
  I use SQL Server via a JDBC connection. I cannot use the calander table.</p>
  
  <p>I've come across various solutions, but none does just what I need it to. This is the kind of list I need, but somehow the results are not correct. I can't find my mistake:</p>
  
  <pre><code>WITH mycte AS
  (
   SELECT DATEADD(ww, DATEDIFF(ww,0,CAST('2010-12-01' AS DATETIME)), 0) DateValue
   UNION ALL
   SELECT  DateValue + 7
   FROM    mycte   
   WHERE   DateValue + 7 &lt; '2016-12-31'
  )
  
  SELECT  DATEPART(wk, DateValue) as week, DATEPART(year, DateValue) as year, DateValue
  FROM    mycte
  OPTION (MAXRECURSION 0);
  </code></pre>
  
  <p>I used --SET DATEFIRST 1; to make sure weeks start on monday.</p>
  
  <p>The result looks like:</p>
  
  <pre><code>week        year        DateValue                 
  ----------- ----------- ------------------------- 
  49          2010        2010-11-29 00:00:00.0     
  50          2010        2010-12-06 00:00:00.0     
  51          2010        2010-12-13 00:00:00.0     
  52          2010        2010-12-20 00:00:00.0     
  53          2010        2010-12-27 00:00:00.0     
  2           2011        2011-01-03 00:00:00.0     
  3           2011        2011-01-10 00:00:00.0     
  4           2011        2011-01-17 00:00:00.0     
  5           2011        2011-01-24 00:00:00.0     
  6           2011        2011-01-31 00:00:00.0
  </code></pre>
  
  <p>The problem is obvious. 2010 hasn't 53 weeks, and week 1 is gone.
  This hapens for other years as well. Only 2015 has 53 weeks.</p>
  
  <p>(note: in iso weeks (Europe) there only 52 weeks in 2010, see wiki: <a href="https://en.wikipedia.org/wiki/ISO_week_date" rel="nofollow">https://en.wikipedia.org/wiki/ISO_week_date</a>)</p>
  
  <blockquote>
    <p>The following 71 years in a 400-year cycle (add 2000 for current
    years) have 53 weeks (leap years, with February 29, are emphasized),
    years not listed have 52 weeks: 004, 009, 015, 020, 026, 032, 037,
    043, 048, 054, 060, 065, 071, 076, 082, 088, 093, 099,105, 111, 116,
    122, 128, 133, 139, 144, 150, 156, 161, 167, 172, 178, 184, 189,
    195,201, 207, 212, 218, 224, 229, 235, 240, 246, 252, 257, 263, 268,
    274, 280, 285, 291, 296,303, 308, 314, 320, 325, 331, 336, 342, 348,
    353, 359, 364, 370, 376, 381, 387, 392, 398.</p>
  </blockquote>
  
  <p>The dates are correct though. 2012-12-27 is a monday and so is 2011-01-03.
  But in Europe we always have a full week 1 (so there is always a monday with weeknumber 1)</p>
  
  <p>Any ideas what hapend to week 1 or why there are so many years with 53 (which is wrong)?</p>