MySQL WEEK Function

MySQL WEEK Function

 MySQL WEEK Function



Summary: in this tutorial, you will learn how to use the MySQL WEEK function to get the week number for a date.

Introduction to MySQL WEEK function

Typically, a year has 365 days for a normal year and 366 days for a leap year. A year is then divided into weeks with each week having an exact 7 days. So for a year we often have 365 / 7 = 52 weeks that range from 1 to 52.

To check whether a particular date belongs to which week number, you use the WEEK function as follows:

WEEK(date, mode);

The WEEK the function accepts two arguments:

  • date is the date that you want to get a week number.
  • mode is an optional argument that determines the logic of week number calculation. It allows you to specify whether the week should start on Monday or Sunday and the returned week number should be between 0 and 52 or 0 and 53.

If you ignore the mode argument, the WEEK the function will use the value of the default_week_format system variable by default.

To get the current value of default_week_format variable, you use the SHOW VARIABLES a statement as follows:

mysql> SHOW VARIABLES LIKE 'default_week_format'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | default_week_format | 0 | +---------------------+-------+ 1 row in set (0.01 sec)

In our server, the default value of  default_week_format is 0. The following table illustrates how the mode argument influences the WEEK function:

ModeFirst day of weekRangeWeek 1 is the first week …
0Sunday0-53with a Sunday in this year
1Monday0-53with 4 or more days this year
2Sunday1-53with a Sunday in this year
3Monday1-53with 4 or more days this year
4Sunday0-53with 4 or more days this year
5Monday0-53with a Monday in this year
6Sunday1-53with 4 or more days this year
7Monday1-53with a Monday in this year

The ” with 4 or more days this year” in the above table means:

  • If the week contains January 1st and has 4 or more days in the new year, the week is numbered as week 1.
  • Otherwise, the week is numbered as the last week of the previous year and the next week is week 1.

The WEEK the function returns a week number followed according to ISO 8601:1988

MySQL WEEK function example

See the following orders the table in the sample database.

The following statement returns the number of orders per week in 2013 using the WEEK function:

SELECT WEEK(orderDate) week_no, COUNT(*) FROM orders WHERE YEAR(orderDate) = 2003 GROUP BY WEEK(orderDate);

In this tutorial, you have learned how to use the MySQL WEEK function to get the week number from a specified date.

Reactions

Post a Comment

0 Comments

close