MySQL: WEEK Function

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

Description

The MySQL WEEK function returns the week portion of a date value.

Syntax

The syntax for the WEEK function in MySQL is:

WEEK( date_value, [ mode ] )

Parameters or Arguments

date_value

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

mode

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

mode	Explanation	Returns
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 WEEK function will return a value between 0-53 or 1-53 depending on the mode specified.
• If you are running MySQL 4.0.14+ and the mode is not specified, the WEEK 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 WEEK function will use 0 as the mode.
• The WEEKOFYEAR function returns the same as the WEEK function with the syntax of WEEK(date_value,3).
• See also the EXTRACT, YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, and MICROSECOND functions.

Applies To

The WEEK 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

Example

Let's look at some MySQL WEEK function examples and explore how to use the WEEK 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 WEEK('2014-01-01');
Output: 0

mysql> SELECT WEEK('2014-04-20');
Output: 16

mysql> SELECT WEEK('2014-07-16');
Output: 28

mysql> SELECT WEEK('2014-10-15');
Output: 41

This last WEEK example would display the week portion of the current system date (current system date is returned by the CURDATE function).

mysql> SELECT WEEK(CURDATE());