MySQL - WEEK() Function

The MYSQL WEEK() function is used to retrieve the calendar week of the given date. This function returns a numerical value, ranging from 0 to 53 or 1 to 53.

In addition to the date value this function accepts another optional parameter representing the mode using which you can specify whether the week should start on Sunday or Monday.

Following are the various possible values of the mode and the respective implications −

Mode First day of Week Week values Range First week of year has...
0 Sunday 0 to 53 Sunday as the first day
1 Monday 0 to 53 More than 3 days
2 Sunday 1 to 53 Sunday as the first day
3 Monday 1 to 53 More than 3 days
4 Sunday 0 to 53 More than 3 days
5 Monday 0 to 53 Monday as the first day
6 Sunday 1 to 53 More than 3 days
7 Monday 1 to 53 Monday as the first day

Syntax

Following is the syntax of MySQL WEEK() function −

WEEK(date);

Parameters

This method accepts the date from which we need to extract the week number as a parameter.

Return value

This function returns an integer representing the week number for the specified date. The week number can range from 1 to 53, depending on the year and the mode used.

Example

In the following example, we are using the MySQL WEEK() function to retrieve the week number for the specified date −

SELECT WEEK('2022-11-20') As Result;

Output

This will produce the following result −

Result
47

Example

The below query will return the week of the specified date (2022-11-20). 1 (Mode) as the second argument defines that the first day of the week is assumed as Monday −

SELECT WEEK('2022-11-20', 1) As Result;

Output

Following is the output −

Result
739575

Example

Here, we are retrieving the week number of the current date −

SELECT WEEK(CURDATE()) As Result;

Output

Following is the output −

Result
47

Example

In the following example, let us create a table named ORDERS using CREATE TABLE statement −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL WEEK() function to calculate the week number for each date value in the "DATE" column.

Result1 is the week number based on the default mode, and Result2 is based on mode 1, considering Monday as the first day of the week.

Select OID, DATE, WEEK(DATE) As Result1, WEEK(DATE, 1) As Result2
From ORDERS;

Output

The output is displayed as follows −

OID DATE Result1 Result2
102 2009-10-08 00:00:00 40 41
100 2009-10-08 00:00:00 40 41
101 2009-11-20 00:00:00 46 47
103 2008-05-20 00:00:00 20 21
Advertisements