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 2023-11-29 48
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 )

Subscribe to our YouTube Channel here

WEEK(): Week Number from Date input

Current Week

Understanding Mode (Optional Parameter , value 0 to 7)

Subscribe