The WEEK() function in MySQL is used to extract the week number from a given date. The function returns a number representing the week of the year, which can range from 0 to 53, depending on the date and the specified starting day of the week.
Definition and Usage
- Purpose: The function calculates the week number for a specified date, based on the week definition used in the calculation.
Syntax
WEEK(date, firstdayofweek)
- Parameters:
- date: Required. The date or datetime value from which to extract the week number.
- firstdayofweek: Optional. Specifies the day on which the week starts. It can be one of the following values:
0- First day of week is Sunday (default)1- First day of week is Monday; the first week of the year has more than 3 days.2- First day of week is Sunday; the first week of the year has more than 3 days.3- First day of week is Monday.4- First day of week is Sunday.5- First day of week is Monday; the first week of the year has more than 3 days.6- First day of week is Sunday.7- First day of week is Monday; the first week of the year has more than 3 days.
Technical Details
- Works in: From MySQL 4.0 onwards.
- Return Type: Returns an integer value representing the week number.
Examples
Example 1: Return the week number for a specific date
SELECT WEEK("2017-06-15");
Result:
24
Explanation: This indicates that June 15, 2017, falls in the 24th week of the year.
Example 2: Return the week number for another date
SELECT WEEK("2017-10-25");
Result:
43
Explanation: October 25, 2017, is in the 43rd week of the year.
Example 3: Return the week number for the current system date
SELECT WEEK(CURDATE());
Result:
<current week number>
Explanation: This will return the week number for the current date based on the current system's date and the specified starting day of the week.
Use Cases
- Weekly Reporting: Useful for generating reports on a weekly basis.
- Data Aggregation: Helpful in aggregating data by week for analysis.
- Scheduling: Can be used in applications that require weekly schedules or timelines.
Related Functions
WEEKOFYEAR(): Returns the week number of the year for a given date, following ISO week date format.YEARWEEK(): Returns the year and week number for a given date.