The WEEKOFYEAR() function in MySQL is used to return the week number for a given date, where the first week of the year is determined as the one that contains the first Thursday of the year. The week number ranges from 1 to 53.
Definition and Usage
- Purpose: The function returns the week number of a given date (from 1 to 53). The first week of the year is the week that contains the first Thursday of the year.
- Note: It assumes that the first day of the week is Monday, and the first week of the year has more than 3 days (according to ISO 8601).
Syntax
WEEKOFYEAR(date)
- Parameters:
- date: Required. The date or datetime value for which you want to extract the week number.
Technical Details
- Works in: From MySQL 4.0 onwards.
- Return Type: Returns an integer between 1 and 53 representing the week number of the year.
Examples
Example 1: Return the week number for a specific date
SELECT WEEKOFYEAR("2017-06-15");
Result:
24
Explanation: June 15, 2017, falls in the 24th week of the year.
Example 2: Return the week number for January 1st, 2017
SELECT WEEKOFYEAR("2017-01-01");
Result:
52
Explanation: January 1, 2017, was on a Sunday and falls in the 52nd week of 2016 (as per the ISO standard).
Example 3: Return the week number for the current system date
SELECT WEEKOFYEAR(CURDATE());
Result:
<week number of today's date>
Explanation: This will return the week number for the current system date.
Use Cases
- Yearly Reports: Useful for generating reports on a weekly basis, especially when working with weeks of the year.
- Date Analysis: Helps in analyzing trends or patterns based on the week of the year.
- ISO Week Numbers: Useful in scenarios where ISO week numbers are required (e.g., for international data analysis).
Related Functions
WEEK(): Returns the week number of the year, but does not follow the ISO week numbering system.DAYOFYEAR(): Returns the day number in the year (1 to 366).