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() returns 0 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.

Tags:

Mysql

Sql

Date