| Bug #71827 | The yearweek() function returns a wrong value | ||
|---|---|---|---|
| Submitted: | 25 Feb 2014 11:26 | Modified: | 9 Dec 2015 18:39 |
| Reporter: | Leif Inge Sandberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.5.35,5.0.96, 5.1.73, 5.5.38, 5.6.18, 5.7.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Feb 2014 11:48]
Valeriy Kravchuk
What is the value of default_weel_formal server variable in your case? mysql> select @@default_week_format; +-----------------------+ | @@default_week_format | +-----------------------+ | 0 | +-----------------------+ 1 row in set (0.00 sec) Check http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_yearweek for details.
[25 Feb 2014 12:02]
Peter Laursen
I believe that this link http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_week explains details better. To achive desired results you may: 1) Set the variable globally. 2) set the variable on a session basis. 3) use the 2nd optional 'mode' parameter to the function call. Peter (not a MySQL/Oracle person)
[25 Feb 2014 12:45]
Leif Inge Sandberg
Thanks for the info.
select @@default_week_format => 0
I set it to any of the valid values (0-7) and get the same results. I set it in these ways; none seemed to have any effect:
set @@default_week_format = 3;
set default_week_format = 3;
set global default_week_format = 3;
Only when I add the mode parameter, I get the correct result:
select yearweek('2012-12-31', 3); -- 201301
select yearweek('2013-01-01', 3); -- 201301
select yearweek('2013-01-06', 3); -- 201301 (Sunday)
select yearweek('2013-01-07', 3); -- 201302 (Monday)
select yearweek('2014-02-25', 3); -- 201409
Note that I did these tests on MySQL 5.5.10, while the former tests were done on 5.5.35.
[25 Feb 2014 12:59]
Peter Laursen
It seems that you are right, On both 5.0.96, 5.1.72, 5.5.35 and 5.6.15 I get the same results:
SHOW VARIABLES LIKE 'default_week_format' -- returns "0"
SELECT YEARWEEK('2012-12-31', 3); -- returns "201301"
SET default_week_format = 3;
SELECT YEARWEEK('2012-12-31'); -- retruns "201253"
So if this variable ever worked as documented (if I understand docs) it looks like it was very long time ago it did.
[7 Mar 2014 8:01]
MySQL Verification Team
Hello Leif, Thank you for the bug report. Verified as described. Thanks, Umesh
[9 Dec 2015 18:39]
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.
Modified description as follows:
The mode argument works exactly like the mode argument to WEEK(). For
the single-argument syntax, a mode value of 0 is used. Unlike WEEK(),
the value of default_week_format does not influence YEARWEEK().
Also corrected the example to say:
mysql> SELECT YEARWEEK('1987-01-01');
-> 198652
Description: select yearweek('2014-02-25') - returns 201408 instead of 201409 select week('2014-02-25') - returns 8 instead of 9 It seems to be caused by incorrectly using week 201253 instead of week 201301. All dates I have tried after this week report one week too low. select yearweek('2012-12-31') - returns 201253 instead of 201301 select week('2013-01-01') - returns 0 instead of 1 We report weekly statistics and now just discovered that all of 2013 and 2014 are one week off. Using Microsoft SQL we get the correct week numbers, but that's not an option right now. How to repeat: Execute the select statements listed in the Description Suggested fix: Week 201301 should start on 2012-12-30.