Bug #38435 LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE
Submitted: 29 Jul 2008 19:36 Modified: 30 Jul 2008 9:43
Reporter: Emery Fabrice NZEYIMANA Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.45 OS:Linux
Assigned to: CPU Architecture:Any
Tags: cast, date, datetime, MICROSECOND
Triage: D5 (Feature request)

[29 Jul 2008 19:36] Emery Fabrice NZEYIMANA
Description:
When a given datetime value contains long Microseconds (like 2008-07-29T10:42:51.3553533+02:00) {with the microseconds part longer than 6 digits}, MySQL fails to make the value a datetime or date. 

SELECT CAST('2008-07-29T10:42:51.3553533+02:00' AS DateTime) 
-----> This gives NULL

But 
SELECT CAST('2008-07-29T10:42:51.355353+02:00' AS DateTime) 
-----> This gives 2008-07-29 10:42:51.355353 as expected

It's true the manual says that the Microseconds part should be nnnnnn (6 digits) but I view this a limitation. I encountered this when using a C# built application (connecting as a webservice client). 

How to repeat:
mysql> SELECT CAST('2008-07-29T10:42:51.3553533+02:00' AS DateTime) AS `TestDateTime` ;
+--------------+
| TestDateTime |
+--------------+
| NULL         | 
+--------------+
1 row in set, 1 warning (0.00 sec)

CREATE TABLE `test_table` 
(
  `somedate` DATETIME NOT NULL
);

INSERT INTO `test_table` VALUES('2008-07-29T10:42:51.3553533+02:00');

mysql> SELECT `somedate` FROM `test_table`;
+---------------------+
| somedate            |
+---------------------+
| 0000-00-00 00:00:00 | 
+---------------------+
1 row in set (0.00 sec)

Suggested fix:
Change DateTime Parsing and take into considerations longer microseconds.
[29 Jul 2008 20:24] Sveta Smirnova
Thank you for the reasonable feature request.
[30 Jul 2008 9:43] Emery Fabrice NZEYIMANA
Hello, 

Is this really a feature request? Or it's a bug that needs to be fixed? 

If we can get 10 from a string like '10abcd' (an unlikely number), I think we should be able to get 2008-07-29 10:42:51 or 2008-07-29 10:42:51.355353 from '2008-07-29T10:42:51.3553533+02:00' (a valid representation of a datetime object). 

Why can't the server at least chop off the milliseconds?
[28 Jan 2009 9:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/64236

2751 Tatiana A. Nurnberg	2009-01-28
      Bug#42146 - DATETIME fractional seconds parse error
      Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE
      
      Parsing of optional microsecond part in datetime did not
      fail gracefully when field width was larger than the allowed
      six places.
      
      Now handles up to the correct six places, and disregards
      any extra digits without messing up what we've already got.
[11 Feb 2009 15:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/65926

2794 Tatiana A. Nurnberg	2009-02-11
      Bug#42146 - DATETIME fractional seconds parse error
      Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE
      
      Parsing of optional microsecond part in datetime did not
      fail gracefully when field width was larger than the allowed
      six places.
      
      Now handles up to the correct six places, and disregards
      any extra digits without messing up what we've already got.
[13 Feb 2009 7:13] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66149

2794 Tatiana A. Nurnberg	2009-02-13
      Bug#42146 - DATETIME fractional seconds parse error
      Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE
            
      Parsing of optional microsecond part in datetime did not
      fail gracefully when field width was larger than the allowed
      six places.
      
      Now handles up to the correct six places, and disregards
      any extra digits without messing up what we've already got.
[13 Feb 2009 18:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/66282

2794 Tatiana A. Nurnberg	2009-02-13
      Bug#42146 - DATETIME fractional seconds parse error
      Bug#38435 - LONG Microseconds cause MySQL to fail a CAST to DATETIME or DATE
      
      Parsing of optional microsecond part in datetime did not
      fail gracefully when field width was larger than the allowed
      six places.
      
      Now handles up to the correct six places, and disregards
      any extra digits without messing up what we've already got.
[19 Feb 2009 13:03] Bugs System
Pushed into 6.0.10-alpha (revid:sergey.glukhov@sun.com-20090218125737-5y5b2xo3duo1wlvo) (version source revid:azundris@mysql.com-20090213181016-5p7fvpozygkoplo9) (merge vers: 6.0.10-alpha) (pib:6)
[13 Mar 2009 19:04] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:vvaintroub@mysql.com-20090218093153-sjzxf01i4ezte0xp) (merge vers: 5.1.33) (pib:6)
[9 May 2009 16:42] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:39] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:37] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)