How to Use WEEK Function in MySQL: Syntax and Examples

In MySQL, use the WEEK(date, mode) function to get the week number of a given date. The optional mode argument controls how weeks are counted, such as which day starts the week and how the first week of the year is defined.

Syntax

The WEEK() function syntax is:

• WEEK(date, mode)

Where:

• date is the date value you want to find the week number for.
• mode is optional and controls the week numbering method (0 to 7).

The mode affects which day is considered the first day of the week and how the first week of the year is calculated.

sql

WEEK(date, mode)

Example

This example shows how to get the week number of a date using different modes:

sql

SELECT
  '2024-06-15' AS sample_date,
  WEEK('2024-06-15') AS default_week,
  WEEK('2024-06-15', 0) AS mode_0_week,
  WEEK('2024-06-15', 1) AS mode_1_week,
  WEEK('2024-06-15', 3) AS mode_3_week;

Output

sample_date | default_week | mode_0_week | mode_1_week | mode_3_week
------------|--------------|-------------|-------------|------------
2024-06-15  | 24           | 24          | 25          | 24

Common Pitfalls

Common mistakes when using WEEK() include:

• Not specifying mode and assuming the default matches your week numbering system.
• Confusing WEEK() with WEEKOFYEAR(), which always uses mode 3.
• Misunderstanding that weeks can start on Sunday or Monday depending on mode.

Example of wrong and right usage:

sql

-- Wrong: Assuming default mode matches ISO week
SELECT WEEK('2024-01-01');

-- Right: Use mode 3 for ISO week numbering
SELECT WEEK('2024-01-01', 3);

Quick Reference

Mode	First Day of Week	First Week of Year	Description
0	Sunday	Week containing Jan 1	Week starts Sunday, first week contains Jan 1
1	Monday	Week containing Jan 1	Week starts Monday, first week contains Jan 1
2	Sunday	First full week	Week starts Sunday, first full week is first week
3	Monday	First full week (ISO 8601)	Week starts Monday, first full week is first week
4	Sunday	Week containing Jan 1	Week starts Sunday, first week contains Jan 1
5	Monday	Week containing Jan 1	Week starts Monday, first week contains Jan 1
6	Sunday	First full week	Week starts Sunday, first full week is first week
7	Monday	First full week (ISO 8601)	Week starts Monday, first full week is first week

Key Takeaways

Use WEEK(date, mode) to get the week number from a date in MySQL.

The mode argument controls how weeks are counted and which day starts the week.

Mode 3 is commonly used for ISO week numbering (weeks start Monday).

Always specify mode if you need consistent week numbering across systems.

WEEKOFYEAR() is a shortcut for WEEK(date, 3) and returns ISO week number.