How to use the MySQL YEARWEEK() function

In this article, we will learn how to use the YEARWEEK() function in MySQL. The YEARWEEK() function 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. This function can be useful for performing calculations or comparisons based on the year and week of the year.

Syntax

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

YEARWEEK(date, mode)

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

The mode parameter is an optional integer that specifies how the year and week are calculated. The possible values are:

If the mode parameter is omitted, the value of the default_week_format system variable is used.

The function returns a four or six digit number, where the first four digits are the year and the last two digits are the week number. For example, 202352 means the 52nd week of the year 2023.

Examples

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

Example 1: Get the year and week number for the current date

The following query returns the year and week number for the current date, using the CURDATE() function to get the current date and the default mode.

SELECT YEARWEEK(CURDATE()) AS year_week;

The output is:

+-----------+
| year_week |
+-----------+
|    202350 |
+-----------+

This means that the current date belongs to the 50th week of the year 2023.

Example 2: Get the year and week number for a specific date with a different mode

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

SELECT YEARWEEK('2023-12-31', 3) AS year_week;

The output is:

+-----------+
| year_week |
+-----------+
|    202401 |
+-----------+

This means that the date ‘2023-12-31’ belongs to the first week of the year 2024, according to the mode 3.

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

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

SELECT DATE_SUB('2023-12-31', INTERVAL WEEKDAY('2023-12-31') - (YEARWEEK('2023-12-31') MOD 10) DAY) AS first_date,
       DATE_ADD('2023-12-31', INTERVAL 6 - WEEKDAY('2023-12-31') + (YEARWEEK('2023-12-31') MOD 10) 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 4: Get the number of weeks in a year

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

SELECT MAX(YEARWEEK(date) MOD 100) 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 default mode.

Related Functions

Some of the related functions that can be used with the YEARWEEK() 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.
• WEEKOFYEAR(): 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.
• YEAR(): Returns the year for a given date or datetime expression.
• MONTH(): Returns the month for a given date or datetime expression, where January is 1 and December is 12.
• DAY(): Returns the day of the month for a given date or datetime expression, between 1 and 31.

Conclusion

In this article, we learned how to use the YEARWEEK() function in MySQL. We saw some examples of how to get the year and 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 YEARWEEK() function.