MySQL: YEARWEEK Function

This tutorial explains how to use the MySQL YEARWEEK function with syntax and examples.

Description

The MySQL YEARWEEK function returns the year and week for a date value.

Syntax

The syntax for the YEARWEEK function in MySQL is:

YEARWEEK( date_value, [ mode ] )

Parameters or Arguments

date_value

A date or datetime value from which to extract the year and week.

mode

Optional. It is used to specify what day the week starts on. It can be one of the following:

mode	Explanation	Week Value
0	First day of the week is Sunday	0-53
1	First day of the week is Monday and the first week has more than 3 days	0-53
2	First day of the week is Sunday	1-53
3	First day of the week is Monday and the first week has more than 3 days	1-53
4	First day of the week is Sunday and the first week has more than 3 days	0-53
5	First day of the week is Monday	0-53
6	First day of the week is Sunday and the first week has more than 3 days	1-53
7	First day of the week is Monday	1-53

Note

• The YEARWEEK function will return a year value as well as a week value (between 0-53 or 1-53) depending on the mode specified.
• The YEARWEEK function may return a year value that is different than the year displayed in the date_value because of the mode specified. This should only happen in the first week of the year and the last week of the year.
• If you are running MySQL 4.0.14+ and the mode is not specified, the YEARWEEK function will use the value in the default_week_format system variable as the mode.
• If you are running a version of MySQL that is older than 4.0.14 and the mode is not specified, the YEARWEEK function will use 0 as the mode.
• See also the EXTRACT, YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, and MICROSECOND functions.

Applies To

The YEARWEEK function can be used in the following versions of MySQL:

• MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23.8

Example

Let's look at some MySQL YEARWEEK function examples and explore how to use the YEARWEEK function in MySQL.

For example: (Note: Your results may vary from the examples below depending on what your default_week_format system variable is set to.)

mysql> SELECT YEARWEEK('2014-01-01');
Output: 201352

mysql> SELECT YEARWEEK('2014-01-05');
Output: 201401

mysql> SELECT YEARWEEK('2014-01-12');
Output: 201402

mysql> SELECT YEARWEEK('2014-07-16');
Output: 201428

mysql> SELECT YEARWEEK('2014-12-31');
Output: 201452

mysql> SELECT YEARWEEK('2015-01-01');
Output: 201452

This last YEARWEEK example would display the year and week for the current system date (current system date is returned by the CURDATE function).

mysql> SELECT YEARWEEK(CURDATE());