title: yearweek on SQL server
body: <p>How can I extract the year and week combination from a date in SQL Server using T-SQL and have it match the MySQL <code>yearweek</code> result? </p>
  
  <p>For example these MySQL queries (<a href="http://sqlfiddle.com/#!2/41bcb/3" rel="nofollow">Click here for MySQL SQL Fiddle</a>):</p>
  
  <pre><code>SELECT yearweek('2013-12-31');
  SELECT yearweek('2014-01-01');
  </code></pre>
  
  <p>This returns <code>201352</code> for both dates. That is the expected result.</p>
  
  <p>But in SQL Server... </p>
  
  <ul>
  <li>datepart works as expected for the year extract</li>
  <li>sometimes datepart does not return the expected value for <code>iso_week</code></li>
  </ul>
  
  <p>The MySQL result cannot be achieved with this T-SQL query...</p>
  
  <pre><code>SELECT datepart(year, @dt) * 100 + datepart (iso_week, @dt);
  </code></pre>
  
  <p>T-SQL versions of the MySQL queries above <a href="http://sqlfiddle.com/#!3/e5b64/3" rel="nofollow">(Click here for T-SQL SQL Fiddle)</a>:</p>
  
  <pre><code>SELECT datepart(year, '2013-12-31') * 100 + datepart (iso_week, '2013-12-31');
  SELECT datepart(year, '2014-01-01') * 100 + datepart (iso_week, '2014-01-01');
  </code></pre>
  
  <p>The result is <code>201352</code> for the first date and <code>201401</code> for the second date.</p>
  
  <p>However, this is not the expected result because...</p>
  
  <ul>
  <li>2014-01-01 belongs to the <strong>last</strong> week of 2013</li>
  <li>So the expected result is <code>201352</code></li>
  </ul>
  
  <p>Do any of you more experienced T-SQL developers know how to extract the year/week of a given date and have this match what I see in MySQL?</p>
  
  <p>I need to have the week start on Monday. This is why I am using <code>iso_week</code>. I have tested the results with <code>week</code> anyway and found the same issue. This query also produces <code>201401</code> when <code>201352</code> is expected.</p>
  
  <pre><code>SELECT datepart(year, '2014-01-01') * 100 + datepart (week, '2014-01-01');
  </code></pre>