Bug #8105 Description of 'mode' parameter in WEEK() function incorrect
Submitted: 24 Jan 2005 10:53 Modified: 26 Jan 2005 16:27
Reporter: Frank Maas (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:current OS:not relevant
Assigned to: Paul DuBois CPU Architecture:Any

[24 Jan 2005 10:53] Frank Maas
Description:
The documentation of the date-time functions states (only relevant bits inserted):

---vv
The function returns the week number for date. The two-argument form of WEEK() allows you to specify whether [...] the return value should be in the range from 0 to 53 or from 1 to 52. [...]

The following table describes how the mode argument works:
  	First day 	  	
Mode 	of week 	Range 	Week 1 is the first week...
0 	Sunday 	0-53 	with a Sunday in this year
1 	Monday 	0-53 	with more than 3 days this year
2 	Sunday 	1-53 	with a Sunday in this year
3 	Monday 	1-53 	with more than 3 days this year
4 	Sunday 	0-53 	with more than 3 days this year
5 	Monday 	0-53 	with a Monday in this year
6 	Sunday 	1-53 	with more than 3 days this year
7 	Monday 	1-53 	with a Monday in this year
---^^

Now the description itself is already strange. Weeks range either from 1 till 52/53 or from 0 till 51/52, which are not the ranges named in the text (0-53 and 1-52). Further, the table does not correspond with the text as it states a range 1-53 and 0-53.

How to repeat:
not relevant

Suggested fix:
Probably the text should be (technical bits not tested, sorry):

The function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday, what week should be considered the first week of the year and whether the return value should be in the range from 0 to 52 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used (or 0 before MySQL 4.0.14). See Section 5.2.3, “Server System Variables”.

The following table describes how the mode argument works:
  	First day 	  	
Mode 	of week 	Range 	Week 1 is the first week...
0 	Sunday 	0-52 	with a Sunday in this year
1 	Monday 	0-52 	with more than 3 days this year
2 	Sunday 	1-53 	with a Sunday in this year
3 	Monday 	1-53 	with more than 3 days this year
4 	Sunday 	0-52 	with more than 3 days this year
5 	Monday 	0-52 	with a Monday in this year
6 	Sunday 	1-53 	with more than 3 days this year
7 	Monday 	1-53 	with a Monday in this year

The mode value of 3 can be used as of MySQL 4.0.5. Values of 4 and above can be used as of MySQL 4.0.17.
[24 Jan 2005 11:25] Frank Maas
Changed version as I am viewing the documentation that is presented via the MySQL website and that reports version 5.0.3 as the version for the documentation.
[26 Jan 2005 16:27] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

A little experimentation with the -01-01 and -12-31 days
of several different years reveals that the ranges in the
table (0-53, 1-53) are correct.

However, the ranges listed in the preceding paragraph
(0 to 53 or 1 to 52) is not correct.  It should say 0 to 53
or 1 to 53. I've changed that.

Thanks for the report.
[26 Jan 2005 20:31] Frank Maas
Paul,

Thanks for testing and adapting the documentation. But your additional information had me puzzled. As years tend to have 52 or 53 weeks, the range 0-53 seems strange. So I experimented a bit and got hold of the meaning, but I would suggest to elaborate a bit more on this subject in the documentation.

My suggestion would be to add a phrase saying something like this:
"If you choose a mode that returns a value in the range 0-53 then the result will be the weeknumber of the year in which <date> falls; the first days of the year always return 0 or 1. The range 1-53 always returns the 'real world' weeknumber, which might be 52 or 53 for the first days of the year."

HTH