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 2025-10-15 41
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

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

WEEK(): Week Number from Date input

Current Week

Understanding Mode (Optional Parameter , value 0 to 7)

Subhendu Mohapatra