title: SQL Server : group by week off by 1 day
body: <p>Using the syntax </p>
  
  <pre><code>dateadd(week, datediff(week, 0, CURRENT_TIMESTAMP),0)
  </code></pre>
  
  <p>to group data by week it appears that the last day of the week gets put into the following week. </p>
  
  <p>In a simple example</p>
  
  <pre><code>print dateadd(week, datediff(week, 0, '27 jan 2012 00:00'),0)
  print dateadd(week, datediff(week, 0, '28 jan 2012 00:00'),0)
  print dateadd(week, datediff(week, 0, '29 jan 2012 00:00'),0)
  print dateadd(week, datediff(week, 0, '30 jan 2012 00:00'),0)
  print dateadd(week, datediff(week, 0, '31 jan 2012 00:00'),0)
  </code></pre>
  
  <p>I would expect to return</p>
  
  <pre><code>Jan 23 2012 12:00AM
  Jan 23 2012 12:00AM
  Jan 23 2012 12:00AM
  Jan 30 2012 12:00AM
  Jan 30 2012 12:00AM
  </code></pre>
  
  <p>or</p>
  
  <pre><code>Jan 22 2012 12:00AM
  Jan 22 2012 12:00AM
  Jan 29 2012 12:00AM
  Jan 29 2012 12:00AM
  Jan 29 2012 12:00AM
  </code></pre>
  
  <p>depending on DATEFIRST settings I guess</p>
  
  <p>however it returns</p>
  
  <pre><code>Jan 23 2012 12:00AM
  Jan 23 2012 12:00AM
  Jan 30 2012 12:00AM
  Jan 30 2012 12:00AM
  Jan 30 2012 12:00AM
  </code></pre>
  
  <p>i.e. the 29 January is in the week of 30th January not 23rd January or 30th January as expected.</p>
  
  <p>I'm clearly missing something but can't think what it might be.</p>