MySQL YEARWEEK() Function

 The YEARWEEK() function in MySQL is used to return the year and week number for a given date. The result is in the format YYYYWW, where YYYY is the year, and WW is the week number. This function can be helpful when you need to group or filter data by year and week.

Syntax

YEARWEEK(date[, mode])

Parameters:

1. date: The date for which you want to calculate the year and week number.
2. mode (optional): Specifies the start of the week and how the week numbering is determined. If omitted, the default value is 0.

The mode parameter values are:

Mode	Week Start Day	Range of Week
0	Sunday	0–53
1	Monday	0–53
2	Sunday	1–53
3	Monday	1–53
4	Sunday	0–53, with fractional years
5	Monday	0–53, with fractional years
6	Sunday	1–53, with fractional years
7	Monday	1–53, with fractional years

Examples

Example 1: Basic Usage

SELECT YEARWEEK('2024-12-24');

Output: 202452
This indicates the 52nd week of the year 2024.

Example 2: Specifying a Mode

SELECT YEARWEEK('2024-12-24', 1);

Output: 202452
Here, the week starts on Monday (mode 1).

Example 3: With Different Dates

SELECT YEARWEEK('2024-01-01', 2);

Output: 202401
This indicates the first week of the year 2024, considering weeks start on Sunday and range from 1 to 53.

Use Cases

1. Grouping Data: Group sales or events by week in a given year.
2. Filtering: Filter records from a specific year and week.
3. Reporting: Generate weekly reports.

Notes

• The behavior of YEARWEEK() depends on the mode; always confirm the mode aligns with your business rules.
• The first week of the year is typically defined by ISO 8601 standards (when using modes like 1 or 3).