MySQL yearweek() vs week() returning different result
Have a look at this example from WEEK
function description:
mysql> SELECT YEAR('2000-01-01'), WEEK('2000-01-01',0); -> 2000, 0
One might argue that
WEEK()
should return 52 because the given date actually occurs in the 52nd week of 1999.WEEK()
returns0
instead so that the return value is “the week number in the given year.”
The above seem to suggest that all modes having 0-53 range would return the week number relative to the year of input date (it'll return 0 if the date falls in the last week of the previous year).
So if first day of year = Monday and first week of year = having 4 or more days this year, then 2018-12-31
belongs to 53rd week of 2018 -and- 1st week of 2019, and the mode parameter determines the return value:
SELECT WEEK('2018-12-31', 1); -- 53
SELECT WEEK('2018-12-31', 3); -- 1
The YEARWEEK
function is unambiguous (the result includes the year) so above does not apply.