I don't know about "incorrect", but confusing, sure. It is easy to
predict what is going to be returned based on the documentation.
On Thu, 2002-11-21 at 14:19, Joe Siegrist wrote:
> I don't agree that mysql is 'right' here though, I realize that if you
> simply strip out the year for the date it would be '01', but if you wrap
> the week number, you shouldn't you increment the year as well? SELECT
> DATE_FORMAT('2001-12-31', '%v-%y'); returning '01-01' is inaccurate to say
> the least.
mysql> select DATE_FORMAT('2001-12-31', '%v-%y');
+-----------------------------------+
| DATE_FORMAT('2001-12-31', '%Y%v') |
+-----------------------------------+
| 200101 |
+-----------------------------------+
In the date_format query given above, the %v (01) is correct, as
2001-12-31 is in the first week of 2002. But the %v doesn't report that
it's giving the week for 2002, it only reports the week. Compare to:
mysql> select yearweek('2001-12-31');
+------------------------+
| yearweek('2001-12-31') |
+------------------------+
| 200201 |
+------------------------+
date_format's %v (and the week() function) prints the week of the year
as extracted from the date, which may fall in the next year.
date_format's %Y prints the year extracted from the date, which, for all
dates in 2001, is 2001. yearweek() (and %X%v, see below) takes both the
year and the date into account when calcuating the value to return, and
date_format's %Y doesn't: each substitution is done independantly of the
other substitutions, ie
set @d = '2001-12-31';
select date_format(@d, '%Y%v');
is equivalent to
select concat(date_format(@d, '%Y'),date_format(@d, '%v'));
but neither of them are necessarily equivalent to
select yearweek(@d);
but that's equivalent to
select date_format(@d, '%X%v');
> %x and %X have the same problem as %v (since they use it).
I don't know if it's actually a "problem", note in docs under
yearweek():
Note that the week number is different from what the WEEK()
function would return (0) as WEEK() returns the week in the
context of the given year.
... that is, the year in the date given to week()/%u (which is why week
can return 53), that is.
The difference between %u and %v is the difference between using
yearweek() and week() with the optional second argument to specify if
the week starts on sunday or monday.
Andy.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
