【数据库-MySql】Unable to convert MySQL date/time value to System.DateTime

  问题背景

Mysql 5.5.20

Entityframework 6.2.0

MySql.Data 6.10.7

MySql.Data.Entity 6.10.7

出错代码

其中 create_time为 timestamp 值为 "0000-00-00 00:00:00"

public partial class mytable
{
    public System.DateTime create_time { get; set; }
}

using (MyEntities db = new MyEntities())
{
  var a =  db.mytable.ToList();
}

报错信息 

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

解决方案一、通过连接字符串配置，将“0000-00-00 00:00:00”转换为"0001-1-1 00:00:00"

A、如果还未添加模型

最后自动生成的连接字符串如下，较正常的字符串增加：convertzerodatetime=True

metadata=res://*/Models.Model2.csdl|res://*/Models.Model2.ssdl|res://*/Models.Model2.msl;provider=MySql.Data.MySqlClient;provider connection string="server=127.0.0.1;user id=root;persistsecurityinfo=True;database=mydb;convertzerodatetime=True"

B、如果已添加模型

1、在《App.config》或《Web.config》添加 连接字符串配置 Convert Zero Datetime=True

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="MyEntitys" connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=127.0.0.1;user id=root;password=root;database=mydb;Convert Zero Datetime=True&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>
</configuration>

分析

C# System.DateTime有最大值"9999-12-31 23:59:59"和最小值"0001-1-1 0:00:00" ，"0000-00-00 00:00:00"小于 System.DateTime的最小值 "0001-1-1 0:00:00",所以导致转换失败。

拓展

环境	版本	数据类型	存储类型	下限	上限
Mysql	5.5.20	timestamp	int 最大值(2147483647)	1970-01-01 08:00:01	2038-01-19 11:14:07
Mysql	5.5.20	datetime		0000-00-00 00:00:00	9999-12-31 23:59:59
C#	2013	System.DateTime		0001-1-1 0:00:00	9999-12-31 23:59:59

 

 

 

 

 

mysql< SELECT FROM_UNIXTIME( 0, '%Y-%m-%d %H:%i:%s' ) 
mysql> 1970-01-01 08:00:00


mysql< SELECT FROM_UNIXTIME( 2147483647, '%Y-%m-%d %H:%i:%s' ) 
mysql> 2038-01-19 11:14:07

mysql< SELECT UNIX_TIMESTAMP('1970-01-01 08:00:00');
mysql> 0

mysql< SELECT UNIX_TIMESTAMP('2038-01-19 11:14:07');
mysql> 2147483647

mysql< SELECT DATE_ADD('1970-01-01',INTERVAL (2147483647-2147483647%(60*60*24) )/(60*60*24) DAY) AS Dates,
FLOOR((2147483647-2147483647%(60*60*24) )/(60*60*24)) AS Days,
FLOOR(8+(2147483647%(60*60*24) - 2147483647%(60*60))/(60*60)) AS Hours,
FLOOR((2147483647%(60*60) - 2147483647%60)/60) AS Minutes,
2147483647%60 AS Seconds;

mysql>
Dates       Days    Hours    Minutes Seconds
2038-01-19	24855	11	     14 	 7

mysql< SELECT DATE_ADD('1970-01-01 08:00:00',INTERVAL 2147483647 SECOND);
mysql> 2038-01-19 11:14:07