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 exactly 7 days. So for a year we often has 365 / 7 = 52 weeks that range from 1 to 52.

The WEEK() function allows you to get the week number of a specific date:

WEEK(date, mode);Code language: SQL (Structured Query Language) (sql)

In this syntax:

• date: This is the date that you want to get a week number.
• mode: This is an optional argument that determines how the function calculates the week number. It specifies whether the week should start on Monday or Sunday and the returned week number should be between 0 and 52 or 0 and 53.

The WEEK() function returns a week number based on ISO 8601:1988.

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:

SHOW VARIABLES LIKE 'default_week_format';Code language: JavaScript (javascript)

Output:

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     |
+---------------------+-------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

The output shows that the default value of  default_week_format is 0.

The following table illustrates how the mode argument influences the WEEK function:

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.

MySQL WEEK() function example

Let’s take some examples of using the WEEK() function.

1) Simple MySQL WEEK() function example

The following example uses the WEEK() function to get the week number of the date '2023-10-16':

SELECT WEEK('2023-10-16') as week_no;Code language: JavaScript (javascript)

Output:

+---------+
| week_no |
+---------+
|      42 |
+---------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

2) Using WEEK() function with table data

We’ll use the orders table in the sample database:

The following statement uses the WEEK() function to get the number of orders per week in 2003:

SELECT 
  WEEK(orderDate) week_no, 
  COUNT(*) 
FROM 
  orders 
WHERE 
  YEAR(orderDate) = 2003 
GROUP BY 
  WEEK(orderDate);Code language: SQL (Structured Query Language) (sql)

Output:

Summary

• Use the MySQL WEEK() function to get the week number from a specific date.