MySQL YEARWEEK() Function

Summary: in this tutorial, you will learn how to use the MySQL YEARWEEK() function to return year and week for a date.

Introduction to the MySQL YEARWEEK() function

The YEARWEEK() function returns the year and week for a date.

Here’s the syntax of the YEARWEEK() function:

YEARWEEK(date)Code language: SQL (Structured Query Language) (sql)

In this syntax:

date: This is the date from which you want to extract the year and week.

The YEARWEEK returns a value in the format yw format. e.g., 202312, 2023 is the year and 12 is the week number. The YEARWEEK() function returns NULL if the date is NULL.

The YEARKWEEK() function also accepts a second argument:

YEARWEEK(date,mode)Code language: SQL (Structured Query Language) (sql)

In this syntax, the mode argument specifies whether the return value should start in the range from 0 to 53 or from 1 to 53.

If you omit the mode argument, the YEARWEEK() function uses the value of the system variable @@default_week_format:

SELECT @@default_week_format;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------------+
| @@default_week_format |
+-----------------------+
|                     0 |
+-----------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that the default week format is 0, meaning that the first day of the week is Sunday, and the range for the week is from 0 to 53.

The following table shows all the valid modes that you can use:

Mode	First Day of Week	Range	Week 1 is the first week when…
0	Sunday	0-53 with Sunday in this year	Any Sunday in the year
1	Monday	0-53 with 4 or more days this year	At least 4 days fall within the year
2	Sunday	1-53 with Sunday in this year	Any Sunday in the year, starting from week 1
3	Monday	1-53 with 4 or more days this year	At least 4 days fall within the year, starting from week 1
4	Sunday	0-53 with 4 or more days this year	Any Sunday in the year, starting from week 1
5	Monday	0-53 with Monday in this year	Any Monday in the year
6	Sunday	1-53 with 4 or more days this year	At least 4 days fall within the year, starting from week 1
7	Monday	1-53 with Monday in this year	Any Monday in the year, starting from week 1

MySQL YEARWEEK function examples

We’ll take some examples of using the MySQL YEARWEEK() function.

1) Simple YEARWEEK() function example

The following example uses the YEARWEEK() function to get the year and week of the date ‘2023-10-17’:

SELECT YEARWEEK('2023-01-01');Code language: SQL (Structured Query Language) (sql)

Output:

+------------------------+
| YEARWEEK('2023-01-01') |
+------------------------+
|                 202301 |
+------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

It returns 202342 where 2023 is the year and 01 is the week number.

2) The YEARWEEK() function example with the mode argument

The following example uses the YEARWEEK() function with the mode 3. It returns a different result based on the rule of the mode 3:

SELECT YEARWEEK('2023-01-01',3);Code language: SQL (Structured Query Language) (sql)

Output:

+--------------------------+
| YEARWEEK('2023-01-01',3) |
+--------------------------+
|                   202252 |
+--------------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

Use the MySQL YEARWEEK() function to return year and week for a date.

Was this tutorial helpful?