The WEEK function in MySQL is a date and time function that is used to extract the week number from a given date. It is particularly useful when working with temporal data and analyzing trends over weeks. The WEEK function returns the week number according to either a Sunday or Monday-based week convention, depending on the mode specified.
Syntax
Here’s the basic syntax of the WEEK function:
WEEK(date, [mode])
date: The input date for which you want to determine the week number.
mode: An optional parameter that specifies the mode for determining the week. This parameter can be either 0 or 1.
If mode is 0 or omitted, the week starts on Sunday, and the week number ranges from 0 to 53.
If mode is 1, the week starts on Monday, and the week number ranges from 0 to 53.
Example
Here’s an example of how you might use the WEEK function:
SELECT WEEK('2024-01-01') AS WeekNumber;
In this example, the function is applied to the date ‘2024-01-01’. The default mode is used (mode 0), which means the week starts on Sunday. The result will be the week number for the given date.
You can also use the WEEK function with the MODE parameter explicitly set:
SELECT WEEK('2024-01-01', 1) AS WeekNumberMondayStart;
In this case, MODE is set to 1, indicating that the week starts on Monday. The result will be the week number for the specified date according to the Monday-based week convention.
It’s important to note that the WEEK function can return 0 or 53 as week numbers in certain situations, depending on the year and the specified mode. Additionally, the behavior of the WEEK function may vary between different versions of MySQL, so it’s always a good idea to refer to the documentation for your specific MySQL version for detailed information and any potential changes.