Skip to main content
Documents
Share
LinkedIn
Facebook
Twitter
Copy
ClausesStatementsKeywordsExpressionsFunctions

MySQL WEEK() Function

The `WEEK()` function in MySQL is used to extract the week number from a given date. It helps in organizing and analyzing data based on weekly intervals.

Usage
The `WEEK()` function is typically used when you need to group or filter records by week. It returns an integer representing the week number of the year for a specified date.

sql
WEEK(date, [mode])

In this syntax, `date` is the date from which to extract the week number, and `mode` is an optional argument that defines the starting day of the week (e.g., Sunday or Monday). If no mode is specified, the default is mode 0, where the week starts on Sunday.

Mode Values

  • 0: Week starts on Sunday. Week 1 is the first week with a Sunday in this year.
  • 1: Week starts on Monday. Follows ISO-8601 standard where week 1 is the first week with at least 4 days in the new year.
  • 2: Week starts on Sunday. Week 1 is the first week with more than half of its days in the new year.
  • 3: Week starts on Monday. Week 1 is the first week with more than half of its days in the new year.
  • 4: Week starts on Sunday. Week 1 is the first week with at least 4 days in the new year.
  • 5: Week starts on Monday. Week 1 is the first week with a Monday in this year.
  • 6: Week starts on Saturday. Week 1 is the first week with a Saturday in this year.
  • 7: Week starts on Sunday. Week 1 is the first week with a Sunday in this year.

Examples

1. Basic Week Extraction

sql
SELECT WEEK('2023-10-04');

This example returns the week number of the date `2023-10-04`. By default, the week starts on Sunday.

2. Week Extraction with Mode

sql
SELECT WEEK('2023-10-04', 1);

Here, the function returns the week number with a mode of `1`, which means the week starts on Monday according to the ISO-8601 standard.

3. Using WEEK() in a Query

sql
SELECT COUNT(*)
FROM orders
WHERE WEEK(order_date) = WEEK(CURDATE());

This example counts the number of orders made in the current week, utilizing `WEEK()` to compare the week number of `order_date` with the current date's week number.

Tips and Best Practices

  • Choose the right mode. Use the `mode` argument to align with your business logic, especially when dealing with international data.
  • Consistent week calculations. Use the same `mode` throughout your queries to maintain consistency in week calculations.
  • Performance considerations. When using `WEEK()` in `WHERE` clauses, consider indexing the date column to improve query performance.
  • Handling weeks spanning two years. Be aware that different modes handle weeks spanning two years differently; ensure your mode choice reflects how you want such weeks treated.
  • Combine with other date functions. Use `WEEK()` alongside functions like `YEAR()` for more granular time-based data analysis.
  • Consider time zones. Be mindful of how time zones might affect date calculations if your dataset includes international data.