Description:
I have seen this about DATE_FORMAT s week-formats,
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
, and this about WEEK s modes:
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
Clearly there is less variation in DATE_FORMAT than in WEEK, but it is not fully documented how.
By an experiment with this outcome,
+------+------+------+------+------+------+------+------+-----------------+
| 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | df |
+------+------+------+------+------+------+------+------+-----------------+
| 13 | 14 | 13 | 14 | 14 | 13 | 14 | 13 | U13 u14 V13 v14 |
+------+------+------+------+------+------+------+------+-----------------+
, I guess that DATE_FORMAT s U, u, V, and v correspond to WEEK s 0, 1, 2, and 3. If this is true, plz document it, but, true or not, plz fully document the format letters s exact meaning (see bug 66178).
In 2013 January 1st fell on Tuesday; therefore, whether the week runs Sun-Sat or Mon-Sun, the week with Jan 1st is the first week by the rule that the week is numbered by the fourth weekday, but the following week is the first week by the rule that the week is numbered by the first weekday. It is not documented, but it looks as if the format letters U and u stand for a week that runs Sun-Sat with the week numbered by the first day, and V and v for a week that runs Mon-Sun _also_ with the week numbered by the fourth day, the European standard.
How to repeat:
set @d=date('2013/4/1');
select week(@d,0) as "0",week(@d,1) as "1",week(@d,2) as "2",week(@d,3) as "3",week(@d,4) as "4",week(@d,5) as "5",week(@d,6) as "6",week(@d,7) as "7", date_format(@d, 'U%U u%u V%V v%v') as df;
Suggested fix:
Fuller documenting of format-letters U, u, V, and v.
(maybe also allowing an digit between '%' and 'U' as in WEEK for all 8: there are requests for more formats, but letters are short)
