Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the number of the week starting from the beginning of the year for the date provided. This may seem simple enough. However, it’s complex because there are one or two more days in a year beyond 52 weeks (i.e., 52×7 = 364); the first day of the year usually isn’t the first day of a week. When a year starts on a Sunday—if you consider Sunday to be the first day of the week—January 1 is definitely the first week of the year. In that case, the function should return 0 or 1 depending on whether you think of 0 as the first number or 1. If you consider Monday the first day of the week, though, then if January 1 is a Sunday, the question is whether you want that day to be considered as part of the last week of the previous year, or just as week 0 of this year and make 1 represent the first full week of the current year. All of these possibilities for MySQL to consider when executing WEEK() are represented by the mode you specify as its second parameter.

The range of values accepted for the function’s second parameter is 0 to 7. Even numbers indicate that Sunday is the first day of the week; odd values indicate Monday is the first day of the week. Codes 0, 1, 4, and 5 return results ranging from 0 to 53; codes 2, 3, 6, and 7 return results ranging from 1 to 53. Codes 0, 2, 5, and 7 determine results of the date given with regard to the year that holds the first day of the week of the week that the first day of the year given is in. Here is an example:

SELECT DAYNAME('2006-01-01') AS 'Day', 
WEEK('2006-01-01', 0) AS '0(S,0)', WEEK('2006-01-01', 1) AS '1(M,0)', 
WEEK('2006-01-01', 2) AS '2(S,1)', WEEK('2006-01-01', 3) AS '3(M,1)', 
WEEK('2006-01-01', 4) AS '4(S,0)', WEEK('2006-01-01', 5) AS '5(M,0)', 
WEEK('2006-01-01', 6) AS '6(S,1)', WEEK('2006-01-01', 7) AS '7(M,1)' 
   UNION ...

+-----------+--------+--------+--------+--------+--------+--------+--------+--------+
| Day       | 0(S,0) | 1(M,0) | 2(S,1) | 3(M,1) | 4(S,0) | 5(M,0) | 6(S,1) | 7(M,1) |
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+
| Sunday    |      1 |      0 |      1 |     52 |      1 |      0 |      1 |     52 | 
| Monday    |      0 |      1 |     53 |      1 |      1 |      1 |      1 |      1 | 
| Tuesday   |      0 |      1 |     52 |      1 |      1 |      0 |      1 |     53 | 
| Wednesday |      0 |      1 |     52 |      1 |      1 |      0 |      1 |     52 | 
| Thursday  |      0 |      1 |     52 |      1 |      0 |      0 |     53 |     52 | 
| Friday    |      0 |      0 |     52 |     53 |      0 |      0 |     52 |     52 | 
| Saturday  |      0 |      0 |     52 |     52 |      0 |      0 |     52 |     52 | 
+-----------+--------+--------+--------+--------+--------+--------+--------+--------+

This results set is created with the SELECT statement shown repeated six times, joined together using UNION to merge the results into one results table. The year is adjusted for each SELECT statement, ranging from 2006 to 2011, and 2014 used in the middle for the Wednesday due to leap year. This chart shows the results of WEEK() for seven different dates (one for each day of the week), all the first day of their respective years. For each date, each row shows the results for each parameter possibility for the WEEK() function. The column headings specify the parameter used, along with whether the parameter considers Sunday or Monday (indicated by S or M, respectively) to be the first day of the week. The 0 just after the S or M indicates that results can range from 0 to 53 weeks; 1 indicates a range of 1 to 53. It’s a complex chart, but the subject is complex and it’s hoped that seeing all of the possibilities will make it easier to understand. Table 12-4 may also be useful in choosing the mode that you want.

If no mode is specified with the WEEK() function, the default is used. The default value is stored in the system variable default_week_format. It can be changed with the SET statement:

SHOW VARIABLES LIKE 'default_week_format';

+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| default_week_format | 0     | 
+---------------------+-------+

SET default_week_format = 1;

As an alternative to WEEK(), you can use YEARWEEK(). It’s synonymous with WEEK(), but with the mode of 3 only.