title: How does DATEDIFF calculate week differences in SQL Server 2005?
body: <p>I would like to calculate the difference in <em>weeks</em> between two dates, where two dates are considered part of the same week if their preceding Sunday is the same. Ideally, I'd like to do this using <code>DATEDIFF</code>, instead of learning an elaborate idiom to calculate the value. But I can't tell how it works when weeks are involved.</p>
  
  <p>The following query returns 1 and 2. This might make sense if your calendar week begins with a Sunday, i.e. if you run <code>SET DATEFIRST 7</code> beforehand or if <code>@@DATEFIRST</code> is 7 by default.</p>
  
  <pre><code>SET DATEFIRST 7;
  -- SET DATEFIRST 1;
  
  DECLARE
      @d1 DATETIME,
      @d2a DATETIME,
      @d2b DATETIME
  ;
  SELECT
      @d1 = '2010-04-05',   -- Monday
      @d2a = '2010-04-16',  -- Following Friday
      @d2b = '2010-04-18'   -- the Sunday following
  ;
  
  SELECT
      DATEDIFF(week, @d1, @d2a) AS weekdiff_a   -- returns 1
      ,DATEDIFF(week, @d1, @d2b) AS weekdiff_b  -- returns 2
  ;
  </code></pre>
  
  <p>So I expected different results if <code>SET DATEFIRST 1</code> is executed instead of <code>SET DATEFIRST 7</code>. But the return values are the same, regardless!</p>
  
  <p>What is going on here? What should I do to get the correct week differences?</p>