Week numbers in MySQL

How to get the week number from a date

To get the ISO week number (1-53) from a date in the column datecol, use SELECT WEEKOFYEAR(datecol) FROM ….

To get the corresponding four-digit year, use SELECT YEARWEEK(datecol, 3) DIV 100 FROM ….

Read more about WEEKOFYEAR() and YEARWEEK() in the MySQL manual.

How to get the number of weeks in a year

To get the number of ISO weeks (i.e. the number of the last week) in a year, get the week number of 28 December in that year using the above logic, i.e. SELECT WEEKOFYEAR(CONCAT(yearcol, '-12-28')) FROM ….

This is based on the fact that the last week of the year always includes 28 December.

Read more

Learn more about week numbers and the ISO week numbering scheme in this little primer.