MySQL WEEK Function

MySQL WEEK() Function

The WEEK() function in MySQL is used to extract the week number from a given date. It returns the week number for the date, which is a useful way to organize or analyze data based on weekly intervals.

Syntax

WEEK(date[, mode])

date: The date value (or expression) from which the week number is to be extracted.
mode (optional): A numeric value that defines the mode of the week numbering. This argument determines the behavior of the function regarding the starting day of the week and whether the week is counted as part of the year.

Week Mode Options

The mode argument controls the calculation of the week number and how the first week of the year is determined. The default is 0, but you can specify other values to handle different standards. Here’s how this mode affects the results:

Mode	Description
`0`	Sunday is the first day of the week (ISO 8601 standard).
`1`	Monday is the first day of the week (same as mode 0, but with different behavior for weeks).
`2`	Sunday is the first day of the week, and the week starts on the first Sunday.
`3`	Monday is the first day of the week (with an alternative rule for the first week).
`4`	Sunday is the first day of the week, counting weeks starting from the first Sunday.
`5`	Monday is the first day of the week (same as mode 3).
`6`	Sunday is the first day of the week.
`7`	Monday is the first day of the week (works the same as mode 1, but for other applications).

For most users, the default mode (0) works fine, where weeks begin on Sunday.

Examples

1. Basic Usage: Extract Week Number (Default Mode 0)

SELECT WEEK('2025-01-12') AS WeekNumber;
-- Output: 2

This extracts the week number for January 12, 2025, using the default mode, where Sunday is the first day of the week.

2. Specify a Mode: Week Starting on Monday

SELECT WEEK('2025-01-12', 1) AS WeekNumber;
-- Output: 2

Here, the a WEEK() function is used with the mode 1, where Monday is considered the first day of the week.

3. Week Number for a Date Using Mode 2 (Sunday First Week)

SELECT WEEK('2025-01-12', 2) AS WeekNumber;
-- Output: 2

In this example, the mode 2 is used, where the week starting on the first Sunday of the year is considered the first week.

4. Week Number for a Date Using Mode 3 (Monday as First Day)

SELECT WEEK('2025-01-12', 3) AS WeekNumber;
-- Output: 2

Here, the result reflects the week number for the specified date using a mode 3, where Monday is the first day of the week.

5. Week Number for Today

SELECT WEEK(CURDATE()) AS CurrentWeek;
-- Output: (Depends on the current date)

This will return the week number for today based on the current date.

Use Cases

Analyzing Weekly Trends
You can use the WEEK() function to group records by week in your queries. This is particularly useful when analyzing data every week.
```
SELECT WEEK(order_date) AS Week, COUNT(*) AS TotalOrders
FROM orders
GROUP BY Week;
```
Scheduling or Reporting Tasks
Extracting week numbers can be helpful in reports or scheduling tasks that are based on weekly intervals.
```
SELECT WEEK(transaction_date) AS WeekNumber, SUM(amount) AS TotalAmount
FROM transactions
GROUP BY WeekNumber;
```
Date Ranges for a Specific Week
You can use the WEEK() function to filter data for a specific week of the year.
```
SELECT * FROM events WHERE WEEK(event_date) = 12;
```

Notes

Week Number Calculation: Depending on the mode, the first week of the year may not always start with January 1st. For example, in mode 1, the first week starts on the Monday of the week containing January 1st.
Week Number Overflow: Week numbers range from 1 to 53 depending on the year, so you may encounter 53 weeks in certain years that have 53 full weeks.
Handling Invalid Dates: If an invalid date is provided, MySQL will return NULL. You can use IFNULL() or COALESCE() to handle such cases.

Conclusion

The WEEK() function in MySQL is a valuable tool for extracting the week number from a date. Whether you're analyzing data weekly, scheduling reports, or organizing information based on the week of the year, this function simplifies the process. By adjusting the mode, you can customize how the week numbers are calculated and choose how your weeks are structured, whether starting from Sunday or Monday.

Souy Soeng

MySQL WEEK Function