WEEKOFYEAR

Description

The WEEKOFYEAR function returns the week number of a specified date within the year (range 1-53). A week starts on Monday and ends on Sunday. Within a year, if a week contains 4 or more days in the current year, that week is considered the 1st week of the current year; otherwise, that week belongs to the last week of the previous year (which may be week 52 or 53).

This function is consistent with the weekofyear function in MySQL.

Syntax

INT WEEKOFYEAR(`<date_or_time_expr>`)

Parameters

Parameter	Description
<date_or_time_expr>	Input datetime value, supports DATE/DATETIME/TIMESTAMP_NS types. For datetime and date formats, please refer to datetime conversion and date conversion

Return Value

Returns INT type week number, range 1-53, representing which week of the year the date belongs to.

• If the week containing January 1st has fewer than 4 days in the current year (e.g., if January 1st is a Wednesday, that week only has January 1-3 in the current year, totaling 3 days), then that week belongs to the previous year, and the 1st week of the current year starts from the next Monday.
• When the week at the end of December has fewer than 4 days total, that week belongs to the first week of the next year
• Input NULL returns NULL

Examples

-- 2023-05-01 is Monday, belongs to week 18 of 2023
SELECT WEEKOFYEAR('2023-05-01') AS week_20230501; 
+---------------+
| week_20230501 |
+---------------+
|            18 |
+---------------+

-- The week from 2023-01-02 to 2023-01-08 contains 7 days in 2023 (≥4), belongs to week 1 of 2023
SELECT WEEKOFYEAR('2023-01-02') AS week_20230102;  
+---------------+
| week_20230102 |
+---------------+
|             1 |
+---------------+

-- 2024-01-01 (Monday) belongs to week 1 of 2024
SELECT WEEKOFYEAR('2024-01-01') AS week_20240101;
+---------------+
| week_20240101 |
+---------------+
|             1 |
+---------------+

-- NULL input (returns NULL)
SELECT WEEKOFYEAR(NULL) AS week_null_input; 
+-----------------+
| week_null_input |
+-----------------+
|            NULL |
+-----------------+