MySQL - YEARWEEK() Function

The MYSQL YEARWEEK() function is used to retrieve the calendar week and year of the given date. This function returns a numerical value which contains the year and week. The return value is in the form of 'YYYYWW'.

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 YEARWEEK() function −

YEARWEEK(date, mode);

Parameters

This method accepts two parameters. The same is described below −

  • date: The date or datetime expression for which you want to determine the year and week.

  • mode (optional): An integer that specifies the mode for the week. It can be 0, 1, 2, 3, 4, 5, 6, or 7.

Return value

This function in MySQL returns the year and week for a given date. The return value format is YYYYWW or YYYYWW.

Example

In the following example, we are using the MySQL YEARWEEK() function to retrieve the calendar week and year of the given date −

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

Output

This will produce the following result −

Result
202247

Example

The below query will return the week and year 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 YEARWEEK('2022-11-20', 1) As Result;

Output

Following is the output −

Result
202246

Example

Here, we are retrieving the week and year of the current date −

SELECT YEARWEEK(CURDATE()) As Result;

Output

Following is the output −

Result
202347

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 YEARWEEK() function to fetch calendar week and year for each date value in the "DATE" column.

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

Output

The output is displayed as follows −

OID DATE Result1 Result2
102 2009-10-08 00:00:00 200940 200941
100 2009-10-08 00:00:00 200940 200941
101 2009-11-20 00:00:00 200946 200947
103 2008-05-20 00:00:00 200920 200921
Advertisements