MySQL: YEARWEEK Function

  • Post author:
  • Post category:MySQL
  • Post comments:1 Comment
MySQL YEARWEEK Function

In this guide, we will explain 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:

modeExplanationWeek Value
0First day of the week is Sunday0-53
1First day of the week is Monday and the first week has more than 3 days0-53
2First day of the week is Sunday1-53
3First day of the week is Monday and the first week has more than 3 days1-53
4First day of the week is Sunday and the first week has more than 3 days0-53
5First day of the week is Monday0-53
6First day of the week is Sunday and the first week has more than 3 days1-53
7First day of the week is Monday1-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 EXTRACTYEARQUARTERMONTHDAYHOURMINUTESECOND, 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.

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');
Result: 201352

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

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

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

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

mysql> SELECT YEARWEEK('2015-01-01');
Result: 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());

Learn More : Click Here

This Post Has One Comment

Leave a Reply