title: Difficulties comprehending ISO_week and week in Microsoft SQL, what's happening exactly?
body: <p>I have a set of data from 8 months or so, which is partly 2012 and partly 2013 data. I'm not sure if this is a cultural thing, but I'm Dutch and I believe the data in the database should be interpreted by <a href="http://en.wikipedia.org/wiki/ISO_8601" rel="nofollow">this</a> standard.</p>
  
  <p>So I've written two queries, one with the regular week expression and one with the ISO_week, this query looks like this:</p>
  
  <pre><code>SELECT datepart(ISO_WEEK,date_dtm) as date_detail,
  datepart(yyyy,date_dtm) as date_year, 
  (sum(sum_value)/sum(user_count)) as value 
  
  FROM kpi_record 
  WHERE kpi_series_id = '15'
  AND date_dtm &gt; dateadd(ww,-12,'2013-02-28 00:00:00.000')
  group by datepart(ISO_WEEK,date_dtm), datepart(yyyy,date_dtm)
  ORDER BY datepart(yyyy,date_dtm), datepart(ISO_WEEK,date_dtm)
  
  --
  
  SELECT datepart(ww,date_dtm) as ww,
  datepart(yyyy,date_dtm) as date_year, 
  (sum(sum_value)/sum(user_count)) as value 
  
  FROM kpi_record 
  WHERE kpi_series_id = '15'
  AND date_dtm &gt; dateadd(ww,-12,'2013-02-28 00:00:00.000')
  group by datepart(yyyy,date_dtm), datepart(ww,date_dtm)
  ORDER BY datepart(yyyy,date_dtm), datepart(ww,date_dtm)
  </code></pre>
  
  <p>The results of these two queries are respectively:</p>
  
  <pre><code>1   2012    7,14
  49  2012    7,31475409836066
  50  2012    7,39261285909713
  51  2012    7,47905477980666
  52  2012    7,30618401206636
  1   2013    7,49925705794948
  2   2013    7,26598837209302
  3   2013    7,24533333333333
  4   2013    7,22245989304813
  5   2013    6,96774193548387
  6   2013    7,24523160762943
  7   2013    7,14718019257221
  8   2013    7,34691195795007
  9   2013    7,23430962343096
  </code></pre>
  
  <p>and</p>
  
  <pre><code>49  2012    7,4537037037037
  50  2012    7,33109017496635
  51  2012    7,4656652360515
  52  2012    7,36874051593323
  53  2012    7,13888888888889
  1   2013    7,50515463917526
  2   2013    7,33190271816881
  3   2013    7,17693315858453
  4   2013    7,24209378407852
  5   2013    7,0201072386059
  6   2013    7,19281914893617
  7   2013    7,17278911564626
  8   2013    7,3283378746594
  9   2013    7,24733096085409
  </code></pre>
  
  <p>My questions, in this order, are:</p>
  
  <ul>
  <li>In the second query (with regular ww), what does this week 53 hold worth of data? It was my understanding that this is a couple of days that entered 2013, yet still belonged to week 52, but due to the year being finished, it adds an extra week for those days. Is this correct? If not, please enlighten me.</li>
  <li>In the first query (iso_week), what is the week 1? I believe it's the week 53 as seen in the second query (regular ww). But it should be week 1 of 2013 then, or maybe added to the first week of 2013? I have trouble understanding what's going on here.</li>
  <li>Even if this is explained, I don't quite understand why 14 rows of data are being returned. With my query, I want to go back in time 12 weeks from the date I provided (hardcoded in example). How can I possibly get 12 rows of legit data of each week, and not 13 or 14? If I use day instead of week, it goes back 12 days and thus I have 12 rows returned to me.</li>
  </ul>
  
  <p>Thanks for your help, I hope I'm making sense.</p>