DbCommand generates SQL in a date format that MySQL does not accept

user_1639615 0
Viewed 121

I have a DbCommand created as such.

DbCommand cmd = conn.CreateCommand();

I then create cmd.CommandText of the format.

"Insert into myTable (`Id`,`myDate`) values (@p0,@p1)"

When dealing with the second parameter (after creating the first) I have code of the form.

var p2 = command.CreateParameter();
p2.ParameterName = "@p2";
p2.Value = myDateTimeObject; //not a string but an actual DateTime object
command.Parameters.Add(p2);

In my code I have the following section to execute DbCommand variables.

int result = cmd.ExecuteNonQuery();

When I actually execute this, it successfully creates the following statement but this statement errors when MySQL tries to execute it.

Insert into myTable (`Id`,`myDate`) values (1,'27/08/2018 16:50:30')

And when I look at the error it is the following.

Error Code: 1292. Incorrect datetime value: '27/08/2018 16:50:30' for column 'myDate' at row 1 0.000 sec

Is there anything I can do to ensure it runs with valid SQL that MySQL will accept? I'm happy to provide more information if necessary.

mysql database c#
1 Answers

I'm guessing that you're running in a non-en-US locale, and are encountering MySQL bug #80011.

One workaround would be to format the DateTime yourself in a format MySQL Server can understand:

var p2 = command.CreateParameter();
p2.ParameterName = "@p2";
p2.Value = myDateTimeObject.ToString("yyyy'-'MM'-'dd' 'HH':'mm':'ss'.'ffffff", CultureInfo.InvariantCulture);
command.Parameters.Add(p2);

The other workaround would be to switch to a different MySQL ADO.NET connector that doesn't have this bug, such as MySqlConnector.

user_236330
Related