Bug #25050 MysqlDateTime

• Submitted: 13 Dec 2006 21:27
• Modified: 14 Dec 2006 18:23
• Reporter: Bojan Saksida
• Status: Not a Bug
• Category: Connector / NET
• Severity: S1 (Critical)
• Version: 5.02
• OS: Windows (Windows)
• CPU Architecture: Any
• Tags: MysqlDateTime

[13 Dec 2006 21:27] Bojan Saksida

Description:
When inserting into sql with DateTime column, and says incorrect datetime values.

How to repeat:
If you have under:
Regional And Language Options->Regional Options->Customize->Date->Short date format: d.M.yyyy

This format is default for slovenian, and i don't want to change to yyyy.MM.dd

If i have d.M.yyyy, I woud get sql exception.

[14 Dec 2006 8:42] Tonci Grgin

Hi Bojan and thanks for your report. I am using Croatian format (dd.MM.yyyy) and don't have problems like reported. 
Can you please post / attach:
 - MySQL server version, host OS version
 - my.ini/cnf
 - NET FW version
 - Small but complete, I presume C#, example exhibiting this error.

[14 Dec 2006 10:17] Bojan Saksida

My project to repeat error:

using namespace System;
using namespace MySql::Data::MySqlClient;
using namespace MySql::Data::Types;

int main(array<System::String ^> ^args)
{
	String ^strConnectString = "server=localhost;user id=root;password=qwertz1; database=mysql; pooling=false";
	MySqlConnection ^conn = gcnew MySqlConnection(strConnectString);
	MySqlCommand ^cmd = gcnew MySqlCommand;
	cmd->Connection = conn;
	conn->Open();
	conn->ChangeDatabase("test");

	String ^sql = "DROP TABLE IF EXISTS test";
	cmd->CommandText = sql;
	cmd->ExecuteNonQuery();
	
	sql = "CREATE TABLE test (JoinDate DateTime NOT NULL)";
	cmd->CommandText = sql;
	cmd->ExecuteNonQuery();

	MySqlDateTime ^dt = MySqlDateTime(DateTime::Now);
	sql = "INSERT INTO test (JoinDate) VALUES ('"+dt+"')";
	cmd->CommandText = sql;
	cmd->ExecuteNonQuery();
    return 0;
}

MYSql Version is 5.0.27 and Connector is 5.0.2 beta. I am using windows XP with sp2.

This is error:

Unhandled Exception: MySql.Data.MySqlClient.MySqlException: #22007Incorrect date
time value: '14.12.2006 11:15:08' for column 'JoinDate' at row 1
   at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
   at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64
& lastInsertId)
   at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior
)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at main(String[] args) in c:\projects\test\test\test.cpp:line 29
   at mainCRTStartupStrArray(String[] arguments) in f:\rtm\vctools\crt_bld\self_
x86\crt\src\mcrtexe.cpp:line 324
Press any key to continue . . .

[14 Dec 2006 10:18] Bojan Saksida

my.ini as requested

Attachment: my.ini (application/octet-stream, text), 9.03 KiB.

[14 Dec 2006 10:19] Bojan Saksida

Oh and I am using c++/cli with .NET Framework 2.0

[14 Dec 2006 12:34] Tonci Grgin

Verified as described by reporter on latest SVN sources.

	        MySqlDateTime dt = new MySqlDateTime(DateTime.Now);
            sql = "INSERT INTO b25050 (JD) VALUES ('" + dt + "')";
	        cmd.CommandText = sql;
	        cmd.ExecuteNonQuery();
produces: Executing command QUERY with text ='INSERT INTO b25050 (JD) VALUES ('14.12.2006 13:25:32')' and the error thrown depends on server mode.

Bojan, consider following code:
DateTime dt;
dt = DateTime.Now;
sql = "INSERT INTO b25050 (JD) VALUES ('" + dt.ToString("yyyy-mm-dd HH-mm-ss") + "')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
produces correct result. As there is convinient workaround this should be S3 or even S4.

[14 Dec 2006 12:40] Tonci Grgin

Sorry Bojan, correct command (and format string) would be:
sql = "INSERT INTO b25050 (JD) VALUES ('" + dt.ToString("yyyy-MM-dd HH:mm:ss") + "')";

[14 Dec 2006 18:23] Reggie Burnett

This is not a bug.  The reason is because you are not using parameters to insert the datetime.  You are assembling the SQL manually using string concatenation and therefore you are responsible for formatting the datetime correctly.  

Let me explain.  Here are the 3 lines that are important:

MySqlDateTime ^dt = MySqlDateTime(DateTime::Now);
sql = "INSERT INTO test (JoinDate) VALUES ('"+dt+"')";
cmd->CommandText = sql;

The first line creates a MySqlDateTime object.  The second line uses string concatentation to create a SQL string. It calls the .ToString() method on MySqlDateTime which will output the datetime in whatever regional setting you have set which may or may not match what MySQL requires for datetime formatting.

To fix this, use "INSERT INTO test (JoinDate) VALUES (?date)" as the sql and then add the parameter to the command.