Bug #64377 DCR: DateTime 0000/00/00 00:00:00 should be Nullable<DateTime> not MinTime
Submitted: 19 Feb 2012 10:46 Modified: 28 Mar 2012 20:57
Reporter: asdf asdfd Email Updates:
Status: No Feedback Impact on me:
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.6.4 OS:Windows (Win2k8r2)
Assigned to: Assigned Account CPU Architecture:Any
Tags: ConvertZeroDateTime, MysqlDateTime

[19 Feb 2012 10:46] asdf asdfd
The MySql.Data.MySqlClient.MySqlDataReader.IsDbNull(int) will crash out if the column is a DateTime and has value of 0000/00/00 00:00:00.

This is due to GetValue(int) not being able to convert the string into a System.DateTime object. The recommended solution to getting past this issue is to update the Connection String to include 'Convert Zero Datetime=true' which then gives causes the value DateTime.MinValue to be returned.

If this value is then used in a compare operation against the same row the value the compare fails due to 0001/01/01 00:00:00 != 0000/00/00 00:00:00

How to repeat:
create table MyTable{ col as datetime } 
insert into MyTable(col)values('0000/00/00 00:00:00')

MySqlCommand("select col from MyTable");

Suggested fix:
Having an option to return Nullable<DateTime> would resolve the problem and correctly reflect what a time of '0' is trying to signify.
[19 Feb 2012 10:57] asdf asdfd
Additional information available at:
[28 Feb 2012 20:57] Gabriela Martinez Sanchez
Dates range differences between Mysql and the .net framework are clearly different. So for this case I would recommend you to use the GetMySqlDateTime instead of the GetValue method, this way will give you the accurate table value instead of having the limitation of the DateTime type of the .net framework. Please check it and let us know if this option works for you or if you have any other comments. (http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqldatareader.html#connect...).

[29 Mar 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".