A MySQL zero date or out-of-range value breaks DateTime reads in .NET
Unable to convert MySQL date/time value to System.DateTimeShort 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.
Fix it fast
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';2Match what you see, then jump to its fix
- 1
The exception appears only on rows whose column shows 0000-00-00Zero dates in a DATE or DATETIME columnFix - 2
The 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
The bad values were written by your code, for example DateTime.MinValueA .NET value that MySQL cannot storeFix
- 1
What does the error look like?
Unable to convert MySQL date/time value to System.DateTime0000-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=true3Verify
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
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=true3Verify
The read that threw now returns a value.
var v = reader.GetDateTime("created_at");Confirmed by mysqlconnector.net
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
EF Core logs 'An error occurred using the connection to database' with MySQL
Can't connect to MySQL server on 'host_name' (111)
This EF Core message is only a wrapper.
3 causes, each with a fixDatabases
DbContextOptionsBuilder has no UseMySql method
Cannot find the UseMysql method on DbContextOptions
UseMySql comes from a provider package, not from EF Core itself.
3 causes, each with a fixDatabases
There is already an open DataReader on the MySQL connection
The error appears inside a while (reader.Read()) loop
One MySQL connection runs one operation at a time.
3 causes, each with a fixDatabases
C# INSERT fails with 'Fatal error encountered during command execution'
MySql.Data.MySqlClient.MySqlException: Fatal error encountered during command execution
A fatal error during command execution means the connection broke while the INSERT was in flight.
3 causes, each with a fixDatabases
MySqlException: Timeout expired in a .NET app
MySql.Data.MySqlClient.MySqlException: Timeout expired
Timeout expired means a wait ended before it finished.
3 causes, each with a fixDatabases
C# cannot reach MySQL: connection attempt failed, no response in time
System.Net.Sockets.SocketException (10060)
SocketError 10060 means the connection attempt timed out, or the host did not respond.
3 causes, each with a fix