How to use the MySQL WEEKOFYEAR() function

In this article, we will learn how to use the WEEKOFYEAR() function in MySQL. The WEEKOFYEAR() function returns the week number for a given date, based on the ISO 8601 standard. The ISO 8601 standard defines that the first week of the year is the week that contains the first Thursday of the year, and the week starts on Monday. This function can be useful for performing calculations or comparisons based on the week of the year.

Syntax

The syntax of the WEEKOFYEAR() function is as follows:

WEEKOFYEAR(date)

The date parameter can be any valid date or datetime expression. If the date is not a valid date, the function returns NULL.

Examples

Let’s see some examples of how to use the WEEKOFYEAR() function in MySQL.

Example 1: Get the week number for a specific date

The following query returns the week number for the date ‘2023-12-31’, which is a Sunday.

SELECT WEEKOFYEAR('2023-12-31') AS week_number;

The output is:

+-------------+
| week_number |
+-------------+
|          52 |
+-------------+

This means that the date ‘2023-12-31’ belongs to the 52nd week of the year 2023.

Example 2: Get the first and last date of a specific week

We can use the WEEKOFYEAR() function to get the first and last date of a specific week. For example, the following query returns the first and last date of the 52nd week of the year 2023.

SELECT DATE_SUB('2023-12-31', INTERVAL WEEKDAY('2023-12-31') DAY) AS first_date,
       DATE_ADD('2023-12-31', INTERVAL 6 - WEEKDAY('2023-12-31') DAY) AS last_date;

The output is:

+------------+------------+
| first_date | last_date  |
+------------+------------+
| 2023-12-25 | 2023-12-31 |
+------------+------------+

This means that the 52nd week of the year 2023 starts on Monday, ‘2023-12-25’ and ends on Sunday, ‘2023-12-31’.

Example 3: Get the number of weeks in a year

We can use the WEEKOFYEAR() function to count the number of weeks in a year. For example, the following query returns the number of weeks in the year 2023.

SELECT MAX(WEEKOFYEAR(date)) AS weeks_in_year
FROM (
  SELECT DATE('2023-01-01') + INTERVAL n DAY AS date
  FROM (
    SELECT a.N + b.N * 10 + c.N * 100 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS c
  ) AS t
  WHERE n BETWEEN 0 AND 364
) AS t;

The output is:

+---------------+
| weeks_in_year |
+---------------+
|            52 |
+---------------+

This means that the year 2023 has 52 weeks, according to the ISO 8601 standard.

Related Functions

Some of the related functions that can be used with the WEEKOFYEAR() function are:

• WEEK(): Returns the week number for a given date, based on a specified mode. The mode can be different from the ISO 8601 standard, depending on the value of the default_week_format system variable or the optional argument.
• YEARWEEK(): Returns the year and week number for a given date, based on a specified mode. The mode can be different from the ISO 8601 standard, depending on the value of the default_week_format system variable or the optional argument.
• WEEKDAY(): Returns the index of the weekday for a given date, where Monday is 0 and Sunday is 6.
• DAYOFWEEK(): Returns the index of the weekday for a given date, where Sunday is 1 and Saturday is 7.
• DAYNAME(): Returns the name of the weekday for a given date.

Conclusion

In this article, we learned how to use the WEEKOFYEAR() function in MySQL. We saw some examples of how to get the week number, the first and last date, and the number of weeks for a given date or year. We also learned about some related functions that can be used with the WEEKOFYEAR() function.