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
$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):
SELECT (YEAR(DATEADD(day, 26 - DATEPART(isoww, datefield), datefield))*100+DATEPART(isowk, datefield)) as theWeek FROM myTable