DateTime.MaxValue saves as Zero in mysql

Question

I am using C# and MySql. I have a requirement where I need to save DateTime.MaxValue to one of the column.

ADO.NET code gives me below value for DateTime.MaxValue

12/31/9999 11:59:59 PM

When I save this in mysql, I see that the value for that datetime(3) column is saved as:

0000-00-00 00:00:00.000

Sample ADO.NET Code

DateTime time = DateTime.MaxValue;

sqlCommand.Parameters.AddWithValue("Expires", time);
sqlCommand.ExecuteNonQuery();

DataType of the column is datetime(3)

I still cannot figure it out why DateTime.MaxValue is saved as 0000-00-00 00:00:00.000

Any thoughts around this?

Answer 1

A DATETIME column can store values up to '9999-12-31 23:59:59'. DateTime.MaxValue is actually 9999-12-31 23:59:59.9999999. When you try to insert it, the fractional seconds overflow the maximum size of the field.

Normally (in STRICT mode), MySQL Server would issue a datetime field overflow error. But if you're running your server in ANSI mode, the overflow is silently converted to the "invalid" date time value 0000-00-00.

One way to fix this problem is to use STRICT mode in your MySQL Server.

Another way is to specify the column type as DATETIME(6), which allows the fractional seconds to be stored.

A third way is to truncate the fractional seconds from your DateTime objects in C# before inserting them in the database.