In MySQL, you can use the WEEKOFYEAR() function to return the calendar week for a given date. Simply provide the date as an argument and the function will return the calendar week as a number in the range from 1 to 53.

Syntax

The syntax goes like this:

WEEKOFYEAR(date)

Where date is the date you want the calendar week returned from.

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT WEEKOFYEAR('2021-01-21') AS 'Week of the year';

Result:

+------------------+
| Week of the year |
+------------------+
|                3 |
+------------------+

Here’s an example using a later date:

SELECT WEEKOFYEAR('2021-12-21') AS 'Week of the year';

Result:

+------------------+
| Week of the year |
+------------------+
|               51 |
+------------------+

Example 2 – WEEKOFYEAR() vs WEEK()

The WEEKOFYEAR() function is the equivalent of using WEEK(date,3). In this context, 3 is the mode that determines whether the week starts on Sunday or Monday, and whether its count ranges from 0 to 53 or from 1 to 53.

Here’s an example that shows these two functions side by side:

SET @date = '2021-07-21';
SELECT 
  WEEK(@date, 3),
  WEEKOFYEAR(@date);

Result:

+----------------+-------------------+
| WEEK(@date, 3) | WEEKOFYEAR(@date) |
+----------------+-------------------+
|             29 |                29 |
+----------------+-------------------+

To see how the mode affects the result, see WEEK() Examples – MySQL.