Which mode of MySQL WEEK() complies with ISO 8601 and matches PHP’s DATE function

Published

To mix MySQL week numbers with PHP, can be a bit confusing. The best way to get a relevant match is to use mode 3 in the MySQL WEEK() function.

For example, in PHP:
[php]
date(‘W’, strtotime(‘2019-12-31’));
Returns: 01

date(‘W’, strtotime(‘2020-12-31’));
Returns: 53

date(‘W’, strtotime(‘2021-12-31’));
Returns: 52
[/php]

The same in MySQL:
[sql]
SELECT WEEK(‘2019-12-31’, 3);
Returns: 1

SELECT WEEK(‘2020-12-31’, 3);
Returns: 53

SELECT WEEK(‘2021-12-31’, 3);
Returns: 52
[/sql]

When mixing up the data in PHP, you might have to at a leading zero to weeks 1 to 9 to match the MySQL week numbers with PHP’s week numbers.

Source: StackOverflow