# 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 leap year. A year is then divided into weeks with each week has exact 7 days. So for a year we often has 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);`

Code language: SQL (Structured Query Language) (sql)

The `WEEK`

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`

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`

statement as follows:

`mysql> SHOW VARIABLES LIKE 'default_week_format';`

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| default_week_format | 0 |

+---------------------+-------+

1 row in set (0.01 sec)

Code language: SQL (Structured Query Language) (sql)

In our server, the default value of `default_week_format`

is 0. The following table illustrates how the `mode`

argument influcences the `WEEK`

function:

Mode | First day of week | Range | Week 1 is the first week … |
---|---|---|---|

0 | Sunday | 0-53 | with a Sunday in this year |

1 | Monday | 0-53 | with 4 or more days this year |

2 | Sunday | 1-53 | with a Sunday in this year |

3 | Monday | 1-53 | with 4 or more days this year |

4 | Sunday | 0-53 | with 4 or more days this year |

5 | Monday | 0-53 | with a Monday in this year |

6 | Sunday | 1-53 | with 4 or more days this year |

7 | Monday | 1-53 | with a Monday in this year |

The ” `with 4 or more days this year`

” in the above table means:

- If the week that 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`

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

## MySQL WEEK function example

See the following `orders`

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);

Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the MySQL `WEEK`

function to get the week number from a specified date.