MySQL YEARWEEK() Function

The YEARWEEK() function in MySQL returns the year and week number (a number from 0 to 53) for a given date or datetime. It combines the year and week number into a single number, which can be useful for reporting or grouping data by year and week.

Definition and Usage

• Purpose: The function returns the year and the week number of the specified date or datetime value.
• Note: Week numbers range from 0 to 53, depending on the year and the specified week start day.

Syntax

YEARWEEK(date, firstdayofweek)

• Parameters:
  • date: Required. The date or datetime value from which you want to extract the year and week number.
  • firstdayofweek: Optional. Specifies the first day of the week. The options are:
    • 0: First day of the week is Sunday.
    • 1: First day of the week is Monday, and the first week of the year must have more than 3 days.
    • 2: First day of the week is Sunday.
    • 3: First day of the week is Monday, and the first week of the year must have more than 3 days.
    • 4: First day of the week is Sunday, and the first week of the year must have more than 3 days.
    • 5: First day of the week is Monday.
    • 6: First day of the week is Sunday, and the first week of the year must have more than 3 days.
    • 7: First day of the week is Monday.

Technical Details

• Works in: From MySQL 4.0 onwards.
• Return Type: Returns an integer, combining both the year and the week number. The format is YYYYWW, where YYYY is the year and WW is the week number.

Examples

Example 1: Return the year and week number for a specific date

SELECT YEARWEEK("2017-06-15");

Result:

201725

Explanation: June 15, 2017, falls in the 25th week of the year 2017.

Example 2: Return the year and week number for the current system date

SELECT YEARWEEK(CURDATE());

Result:

<current year and week number>

Explanation: This will return the year and the week number for the current system date.

Use Cases

• Group Data by Week: Useful when you need to group or filter data by the week number within a given year.
• Reporting: Often used in weekly reports to represent data grouped by year and week.
• Date Calculations: Useful for comparing or calculating time periods based on weeks of the year.

Related Functions

• YEAR(): Extracts the year from a date or datetime.
• WEEK(): Returns the week number for a given date.
• WEEKOFYEAR(): Returns the week number (1 to 53) for a given date.