Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A MySQL zero date or out-of-range value breaks DateTime reads in .NET

ErrorUnable to convert MySQL date/time value to System.DateTime

Short answer

MySQL can hold date values that .NET DateTime cannot, the common one being the zero date 0000-00-00. By default the connector throws when it reads one. Set Convert Zero Datetime to return DateTime.MinValue, set Allow Zero Datetime to get a MySqlDateTime, or clean the stored values.

  • Applies to Connector/NET 8.0, MySqlConnector 2.x, MySQL 8.4, .NET 8
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find out whether the table really holds zero dates. Run this in the mysql client, using your own table and column names.

    SELECT COUNT(*) FROM orders WHERE created_at = '0000-00-00 00:00:00';
  2. 2Match what you see, then jump to its fix

    1. 1The exception appears only on rows whose column shows 0000-00-00Zero dates in a DATE or DATETIME columnFix
    2. 2The same zero-date exception, with a connection string that has no zero-date optionYour project uses the MySqlConnector package instead of Oracle Connector/NETFix
    3. 3The bad values were written by your code, for example DateTime.MinValueA .NET value that MySQL cannot storeFix

What does the error look like?

  • Unable to convert MySQL date/time value to System.DateTime
  • 0000-00-00 00:00:00 in the column that fails to read

Cause 1 of 3

Zero dates in a DATE or DATETIME column

1Check

Oracle documents that with AllowZeroDateTime false, the default, a System.DateTime is returned for valid values and an exception is thrown for disallowed values such as zero datetime.

SELECT id FROM orders WHERE created_at = '0000-00-00 00:00:00' LIMIT 10;

2Fix

Pick one behaviour. Convert Zero Datetime=true makes GetValue and GetDateTime return DateTime.MinValue for such values. Allow Zero Datetime=true makes GetValue return a MySqlDateTime object for them instead.

server=localhost;port=3306;user id=app;password=...;database=test;convert zero datetime=true

3Verify

Run the query that failed. It returns rows, and the bad ones show 0001-01-01 00:00:00.

var d = reader.GetDateTime(0); Console.WriteLine(d);

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

Your project uses the MySqlConnector package instead of Oracle Connector/NET

1Check

MySqlConnector documents ConvertZeroDateTime and AllowZeroDateTime, both false by default. With AllowZeroDateTime on, date columns return MySqlDateTime objects, so the zero date can be read.

grep -R "ConvertZeroDateTime\|AllowZeroDateTime" --include=*.json --include=*.config .

2Fix

Add ConvertZeroDateTime=true to the connection string if you want DateTime.MinValue back, or AllowZeroDateTime=true if you want MySqlDateTime.

Server=localhost;Database=test;User ID=app;Password=...;ConvertZeroDateTime=true

3Verify

The read that threw now returns a value.

var v = reader.GetDateTime("created_at");

Confirmed by mysqlconnector.net

Did this fix it?

Cause 3 of 3

A .NET value that MySQL cannot store

1Check

MySqlConnector's DateTime storage page says TIMESTAMP covers roughly 1970 to 2038 and DATETIME covers 1000 to 9999, so DateTime.MinValue (0001-01-01) is outside both.

SELECT MIN(created_at), MAX(created_at) FROM orders;

2Fix

Stop writing out-of-range values. Store NULL for unknown dates, or a real date inside the supported range, and use DATETIME(6) when you need fractional seconds.

ALTER TABLE orders MODIFY created_at DATETIME(6) NULL;

3Verify

New rows read back without an exception.

SELECT created_at FROM orders ORDER BY id DESC LIMIT 5;

Confirmed by mysqlconnector.net

Did this fix it?

How do I stop it coming back?

  • Make date columns nullable and write NULL, not a placeholder date, when the value is unknown.
  • Never send DateTime.MinValue or DateTime.MaxValue to a MySQL column.
  • Pick one connection-string option (convert or allow) and keep it the same across services that read the table.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.