MySQL Bugs: #64686: DateTime(3) is not processed correctly

Bug #64686	DateTime(3) is not processed correctly
Submitted:	18 Mar 2012 22:26	Modified:	18 Jun 2012 21:09
Reporter:	Bart Vries	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.5.4	OS:	Windows
Assigned to:	Gabriela Martinez Sanchez	CPU Architecture:	Any

Description:
Inserting a .net datetime with milliseconds into a datetime(3) does not result in the proper value in the database. Tested against mysql 5.6.4-m7. Run the code below and the execute "select * from test;" to look at the inserted data in the database. This results in:
+----+-------------------------+
| id | date                    |
+----+-------------------------+
|  1 | 2012-03-18 23:09:07.600 |
+----+-------------------------+

How to repeat:
using(var con = new MySqlConnection(@"Server=localhost;Database=streamstore;Uid=root;Pwd=;"))
{
  con.Open();
  using(var com = con.CreateCommand())
  {
    com.CommandText = @"CREATE TABLE IF NOT EXISTS test (id int primary key, date datetime(3));";
    com.ExecuteNonQuery();
  }

  using (var com = con.CreateCommand())
  {
    com.CommandText = @"INSERT test values(@id, @date)";
    com.Parameters.AddWithValue("@id", 1);
    com.Parameters.AddWithValue("@date", new DateTime(2012, 3, 18, 23, 9, 7, 6));
    com.ExecuteNonQuery();
  }
}

Suggested fix:
Make sure the exepcted vaalue is returned. The expected behaviour is:
+----+-------------------------+
| id | date                    |
+----+-------------------------+
|  1 | 2012-03-18 23:09:07.006 |
+----+-------------------------+

Hi Bart, thanks for your report. I did verify the bug and we're working on the fix. In the mean time I found a workaround so you can still be able to see the milliseconds working. You need to set the fsp to 6 instead of 3. And also since you won't be able to put a decimal value on the DateTime constructor you can specify the datetime value including milliseconds as a string in the MySqlParameter constructor. I hope this will help you to work while we publish the fix for this bug. Please let me know if you have any questions or comments.

Added to changelog for 6.5.5: 

The milliseconds portion of a date/time value was not stored
correctly for the datatype DATETIME(3).