Bug #28623 Problem on date function

• Submitted: 23 May 2007 15:48
• Modified: 24 May 2007 13:21
• Reporter: Michael Douglas
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.0.41-community-nt
• OS: Windows (XP)
• CPU Architecture: Any
• Tags: NOW(), returns not as expected

[23 May 2007 15:48] Michael Douglas

Description:
As mentioned within documentation we use NOW()+0 and received in older mySQL Versions 4.x => YYYYmmddHHiiss   

Now if we using our application we receive a "data too long". After testing 

=> SELECT NOW()+0  
The result is "YYYYmmddHHiiss.000000"

Since the table field is 14 chars so the error message is correct but the documentation is wrong and not compatible with older versions.

How to repeat:
=> SELECT NOW()+0  
The result is "YYYYmmddHHiiss.000000"

Suggested fix:
Change it accordingly to your manual. Or change the documentation itself.

[24 May 2007 13:21] Valeriy Kravchuk

Sorry, but this behaviour is explicitely documented (http://dev.mysql.com/doc/refman/5.0/en/datetime.html):

 As of MySQL 5.0.8, conversion of DATETIME  values to numeric form (for example, by adding +0) results in a double value with a microseconds part of .000000:

mysql> SELECT NOW(), NOW()+0;
+---------------------+-----------------------+
| NOW()               | NOW()+0               |
+---------------------+-----------------------+
| 2007-04-23 14:21:52 | 20070423142152.000000 | 
+---------------------+-----------------------+

Before MySQL 5.0.8, the conversion results in an integer value with no microseconds part.