What mode for MySQL WEEK() complies with ISO 8601

Solution 1:

In ISO week numbering, Monday is the first day of the week, so that alone narrows it down to one of the odd-numbered modes.

Per Wikipedia:

There are mutually equivalent descriptions of week 01:

• the week with the year's first Thursday in it (the formal ISO definition),
• the week with 4 January in it,
• the first week with the majority (four or more) of its days in the starting year, and
• the week starting with the Monday in the period 29 December – 4 January.

The third of those descriptions matches "with more than 3 days this year" from the table above, so now we've narrowed it down to either 1 or 3.

Finally, still from Wikipedia (emphasis added):

If 1 January is on a Monday, Tuesday, Wednesday or Thursday, it is in week 01. If 1 January is on a Friday, Saturday or Sunday, it is in week 52 or 53 of the previous year (there is no week 00).

Hence, the range must be 1-53, not 0-53. This in turn means the right mode is mode 3.

Solution 2:

I know this question is old, but it is well SEO positioned :) So just to make the answer more complete - when displaying the year and week number, you can use this:

DATE_FORMAT(your_date_here, "%x-%v")

it will produce the iso week number for "%v" (1-53) and a correct year number for "%x".

Solution 3:

For France you have to put in file /etc/mysql/my.cnf § [Mysqld] : default_week_format = 3

Notice : dont work for YEARWEEK, needs add mode = 3 SELECT YEARWEEK(CURDATE(), 3)

Pour la France vous devez modifier ou ajouter cette ligne dans le paragraphe [Mysqld] du fichier /etc/mysql/my.cnf : default_week_format = 3 Cela permet à la fonction WEEK de retourner les vraies numéros de semaines Française. Attention pour la fonction YEARWEEK il faut impérativement ajouter le mode à 3 SELECT YEARWEEK(CURDATE(), 3)