Bug #19747 date(datetime field) + 0 only returns year
Submitted: 12 May 2006 2:36 Modified: 12 May 2006 14:43
Reporter: avi weiss Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.9 OS:Windows (Windows XP)
Assigned to: CPU Architecture:Any

[12 May 2006 2:36] avi weiss 
Description:
date(datetime field) returns 'yyyy-mm-dd'. adding 0 should return integer yyyymmdd, but only returns yyyy.

How to repeat:
create datetime field "foo", enter date value, enter "select date(foo) + 0"
[12 May 2006 11:25] Hartmut Holzgraefe 
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

the date string is casted to an integer first which only takes the leading digits into account
(the YYYY digits before the first - here) and then adds a 0 -> expected result
[12 May 2006 14:43] Hartmut Holzgraefe 
Well, actually i now think it is a bug. DATE() returns a string result 
but it should probably be of type DATE instead. The documentation
does not clearly state this so it's at least a docs issue. 

But as i've already filed a bug report on a similar problem with CAST()
i'll merge this into the other report and close this issue as duplicate of
Bug #19765