Bug #41825 Date_Format - works as designed!
Submitted: 2 Jan 2009 9:10 Modified: 2 Jan 2009 11:19
Reporter: Dennis Houchin Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0 OS:Windows (Server 2003 R2)
Assigned to: CPU Architecture:Any
Tags: date_format, invalid date, wrong year

[2 Jan 2009 9:10] Dennis Houchin 
Description:
Date_Format returns incorrect year for first three days of 2009, first 2 days of 2010, first day of 2011, first four days of 2008, first five days of 2007, etc.

How to repeat:
Execute the following SQL:

mysql> select 
       date_format('2009-01-03','%m/%d/%X') as WrongDate,
       date_format('2009-01-04','%m/%d/%X') as RightDate;

+------------+------------+
| WrongDate  | RightDate  |
+------------+------------+
| 01/03/2008 | 01/04/2009 |
+------------+------------+
1 row in set (0.00 sec)

mysql>
[2 Jan 2009 9:43] Valeriy Kravchuk 
Sorry, but this is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format:

"%X 	Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V"

This week started in 2008. Just use %Y:

mysql> select
    ->        date_format('2009-01-03','%m/%d/%Y') as WrongDate;
+------------+
| WrongDate  |
+------------+
| 01/03/2009 |
+------------+
1 row in set (0.00 sec)
[2 Jan 2009 11:19] Dennis Houchin 
RTFM! The perfect answer.  Thanks and apologies.
DH