ISO 8601 Week number in PHP, MySQL and MSSQL

The following methods calculate the ISO 8601 Week number in PHP, MySQL and Microsoft SQL Server, with the same exact results. All the examples below are using the same week criteria: week stars on Monday.

MySQL is easy, as it supports this directly:

SELECT yearweek(datefield,3) as theWeek from myTable

PHP example

$theWeek = date('oW',time());
echo "Today's week number is $theWeek";

Now, the trickier part, SQL Server. SQL Server can output the week number, but not the year, and this is problematic for all the dates in the last and first week of each year. To solve this without using UDF’s (which are slow performancewise):

  (YEAR(DATEADD(day, 26 - DATEPART(isoww, datefield), datefield))*100+DATEPART(isowk, datefield)) as theWeek
  FROM myTable






Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Discover more from try {} except

Subscribe now to keep reading and get access to the full archive.

Continue reading