MySQL in a Nutshell, 2nd Edition

Name

WEEK()

Synopsis

WEEK(date[, value])

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. ...