Bug #91199 "Incorrect datetime value" attempting to use a MySqlDateTime as a parameter
Submitted: 9 Jun 2018 20:38 Modified: 25 Jul 2018 3:42
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:8.0.11 OS:Windows (10)
Assigned to: CPU Architecture:x86 (x64)

[9 Jun 2018 20:38] Bradley Grainger 
Description:
MySQL Server 8.0.11 (Docker image mysql:8.0)
MySql.Data 8.0.11

Attempting to use a MySqlDateTime object as a parameter to MySqlCommand fails with a MySqlException with a message similar to "Incorrect datetime value: '6/9/2018 12:34:56 PM' for column 'ts' at row 1"

Call stack:
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at UserQuery.Main(String[] args)

How to repeat:
Execute the following C# code:

using (var connection = new MySqlConnection("UserID=root;Password=test"))
{
	connection.Open();
	using (var cmd = new MySqlCommand(@"
create schema if not exists test;
drop table if exists test.time;
create table test.time(ts timestamp(6) not null)", connection))
		cmd.ExecuteNonQuery();

	using (var cmd = new MySqlCommand("insert into test.time values(@ts);", connection))
	{
		// NOTE: This works fine: new DateTime(2018, 6, 9, 12, 34, 56, 999)
		// But wrapping it with a MySqlDateTime fails
		cmd.Parameters.AddWithValue("@ts", new MySqlDateTime(2018, 6, 9, 12, 34, 56, 123456));
		cmd.ExecuteNonQuery();
	}
}
[11 Jun 2018 6:46] Chiranjeevi Battula 
Hello Bradley Grainger,

Thank you for the bug report.
This is most likely duplicate of Bug #80011, please see Bug #80011

Thanks,
Chiranjeevi.
[11 Jun 2018 14:31] Bradley Grainger 
I don't know that this is exactly the same bug.

In bug #80011 he says he's using the en-GB locale and adding DateTime.Now. In this example, I'm running in the en-US locale and adding new MySqlDateTime(DateTime.Now).

I explicitly said that adding a new DateTime works fine, but that MySqlDateTime was broken; bug #80011 makes no mention of MySqlDateTime.
[25 Jul 2018 3:42] Bradley Grainger 
Chiranjeevi Battula,

I really think this is a different  issue, directly related to (or caused by) using a MySqlDateTime as a MySqlParameter value. A recent Stack Overflow question shows someone running into a variant of this exact problem: https://stackoverflow.com/q/51489520

Can this case be reopened?