CODEWITHSUNDEEP
c#mysqldatabasedatetimedatetime-format
user2181948
user2181948

Reputation: 1888

Inserting a MySqlDateTime value from C# to a MySQL database

I have a C# application which needs to insert the current datetime to a datetime field in a MySQL database. How do I accomplish this?

I am using MySQL Connector/NET 6.9.9. Below is what I have tried so far. data.currentDateTime is of type MySqlDateTime.

The expected result in the database is the correct datetime, however the actual value is 0000-00-00 00:00:00.

    // Set up data object and add datetime

    MyData data = new MyData();
    data.currentDateTime = new MySqlDateTime(DateTime.Now);

    // Insert into database
    try
    {
        MySqlConnection conn = getMySqlConnection();
        conn.Open();

        MySqlCommand cmd = new MySqlCommand(@"
            INSERT INTO my_data (`current_datetime`)
            VALUES (@currentDateTime)
        ", conn);

        log.Debug(data.currentDateTime); // correct timestamp

        cmd.Parameters.AddWithValue("currentDateTime", data.currentDateTime);
        cmd.ExecuteNonQuery();

       // Value in the database is 0000-00-00 00:00:00

    } catch (Exception e) {
        // Hnadle exception
    }

MyData class:

    public class MyData
    {
        public int id { get; set; }
        public MySqlDateTime currentDateTime { get; set; }
        // Other fields

        public MyData()
        {
            // Empty constructor
        }
    }

Upvotes: 0

Views: 764

Answers (1)

Bradley Grainger
Bradley Grainger

Reputation: 28162

You're encountering a variant of MySQL bug 91199.

With the default SQL mode, MySQL Server will reject the incorrect MySqlDateTime that is serialized by Connector/NET with an Incorrect datetime value error.

However, if your MySQL Server doesn't have NO_ZERO_DATE and strict mode enabled, then attempting to insert a MySqlDateTime will “succeed” but the inserted value will be 0000-00-00 00:00:00.

Since bug 91119 has been incorrectly closed as a duplicate, this probably isn't likely to be fixed soon. As a workaround, you could consider switching to MySqlConnector, an OSS alternative to Connector/NET that fixes this (and many other bugs).

To work around this with MySql.Data, add the underlying DateTime as the parameter value, instead of the MySqlDateTime object:

cmd.Parameters.AddWithValue("currentDateTime", data.currentDateTime.GetDateTime());

Note that this will throw a MySqlConversionException if the MySqlDateTime can't be converted to a DateTime; if this can happen in your code, you'll need to test data.currentDateTime.IsValidDateTime and do something else if that is false.

Upvotes: 1

Related Questions