WEEK() MySQL function to return week number of the year from 0 to 53

SELECT WEEK('2023-01-02'); # 1
SELECT WEEK('2023-12-31'); # 53
SELECT WEEK('2026-08-31'); # 35
SELECT WEEK('2026-02-31'); # ull
SELECT WEEK('2024-01-01',0); # 0
SELECT WEEK('2024-01-01',1); # 1

Current Week

SELECT CURDATE() dt1, WEEK(CURDATE()) dt2

Output is 2026-09-09 36
CURDATE() : Today's Date
WEEK() returns the week number of the year from a date .
Syntax

WEEK(date,[mode])

Example

SELECT WEEK('2015-07-25')

Output is 29
For a invalid date we will get Null as output

SELECT WEEK('2015-02-31')

Output is NULL
DEMO: Select date from Calendar to execute WEEK()

Understanding Mode (Optional Parameter , value 0 to 7)

The week can start on Sunday or Monday,
The week of 1st of Jan should have 4 more days to qualify for week No. 1
Otherwise it will be last week of previous year and next week will be No. 1
In the DEMO page for WEEK() try these days to understand the concepts of Mode.

2012-01-01 change the First Day of Week to Monday
2018-01-01 select Sunday as First Day of Week

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

SQL Date WEEKDAY() : Index of Weekday ( 0 for Monday, 1 Tuesday )

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

WEEK(): Week Number from Date input

Current Week

Understanding Mode (Optional Parameter , value 0 to 7)

Subhendu Mohapatra