无法将 MySQL 日期/时间值转换为 System.DateTime
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5754822/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unable to convert MySQL date/time value to System.DateTime
提问by Ankit Chauhan
I get this error:
我收到此错误：
Unable to convert MySQL date/time value to System.DateTime
无法将 MySQL 日期/时间值转换为 System.DateTime
while I am trying to fetch the data from a MySQL database. I have the datedatatype in my MySQL database. But while retrieving it into my datatable, it get the error above.
当我试图从 MySQL 数据库中获取数据时。我的MySQL 数据库中有日期数据类型。但是在将它检索到我的数据表中时，它得到了上面的错误。
How can I fix this?
我怎样才能解决这个问题？
采纳答案by dkretz
If I google for "Unable to convert MySQL date/time value to System.DateTime" I see numerous references to a problem accessing MySQL from Visual Studio. Is that your context?
如果我在 google 上搜索“无法将 MySQL 日期/时间值转换为 System.DateTime”，我会看到许多对从 Visual Studio 访问 MySQL 的问题的引用。那是你的上下文吗？
One solution suggested is:
建议的一种解决方案是：
This is not a bug but expected behavior. Please check manual under connect options and set "Allow Zero Datetime" to true, as on attached pictures, and the error will go away.
这不是错误而是预期的行为。请检查连接选项下的手册并将“允许零日期时间”设置为 true，如附加图片，错误将消失。
Reference: http://bugs.mysql.com/bug.php?id=26054
回答by agni
You must add Convert Zero Datetime=Trueto your connection string, for example:
您必须添加Convert Zero Datetime=True到您的连接字符串，例如：
server=localhost;User Id=root;password=mautauaja;Persist Security Info=True;database=test;Convert Zero Datetime=True
回答by Kiddo
i added both Convert Zero Datetime=True& Allow Zero Datetime=Trueand it works fine
我添加了Convert Zero Datetime=True&Allow Zero Datetime=True并且它工作正常
回答by Tim Meers
Pull the datetime value down as a string and do a DateTime.ParseExact(value, "ddd MMM dd hh:mm:ss yyyy", culture, styles);You would just need to set the date format up for the date you are returning from the database. Most likely it's yyyy-MM-dd HH:mm:ss. At least is is for me.
将日期时间值作为字符串下拉并执行 aDateTime.ParseExact(value, "ddd MMM dd hh:mm:ss yyyy", culture, styles);您只需要为从数据库返回的日期设置日期格式。很可能是yyyy-MM-dd HH:mm:ss。至少对我来说是。
Check here more info on the DateTime.ParseExact
在这里查看更多信息 DateTime.ParseExact
回答by Jakob Alexander Eichler
Let MySql convert your unix timestamp to string. Use the mysql function FROM_UNIXTIME( 113283901 )
让 MySql 将您的 unix 时间戳转换为字符串。使用 mysql 函数 FROM_UNIXTIME( 113283901 )
回答by Singaravelan
I also faced the same problem, and get the columns name and its types. Then cast(col_Name as Char) from table name. From this way i get the problem as '0000-00-00 00:00:00' then I Update as valid date and time the error gets away for my case.
我也遇到了同样的问题，并获取了列名及其类型。然后从表名转换（col_Name as Char）。通过这种方式，我得到的问题是 '0000-00-00 00:00:00' 然后我更新为有效日期和时间，错误消失了。
回答by KHALID
You can make the application fully compatible with the date and time that is used by MySql. When the application runs at runtime provide this code. First Go to the Application Events. In the list of tools
您可以使应用程序与 MySql 使用的日期和时间完全兼容。当应用程序在运行时运行时提供此代码。首先转到应用程序事件。在工具列表中
- Go to the project
- Project Properties
- Select the Application tab
- View Application Events
- 前往项目
- 项目属性
- 选择应用程序选项卡
- 查看应用程序事件
This will open a new file. This file contains code used at the start of the application.
这将打开一个新文件。此文件包含在应用程序启动时使用的代码。
Write this code in that new file:
在该新文件中写入以下代码：
Partial Friend Class MyApplication
Private Sub MyApplication_Startup(ByVal sender As Object, ByVal e As Microsoft.VisualBasic.ApplicationServices.StartupEventArgs) Handles Me.Startup
My.Application.ChangeCulture("en")
My.Application.ChangeUICulture("en")
My.Application.Culture.DateTimeFormat.ShortDatePattern = "yyyy-MM-dd"
My.Application.Culture.DateTimeFormat.LongDatePattern = "yyyy-MM-dd"
My.Application.Culture.DateTimeFormat.LongTimePattern = "HH:mm:ss"
My.Application.Culture.DateTimeFormat.ShortTimePattern = "HH:mm:ss"
End Sub
End Class
回答by P Walker
Rather than changing the connection string, you can use the IsValidDateTimeproperty of the MySqlDateTimeobject to help you determine if you can cast the object as a DateTime.
您可以使用对象的IsValidDateTime属性MySqlDateTime来帮助您确定是否可以将对象转换为DateTime.
I had a scenario where I was trying to load data from an "UpdateTime" column that was only explicitly set when there was an update to the row (as opposed to the InsertedTime which was always set). For this case, I used the MySqlDataReader.GetMySqlDateTimemethod like so:
我有一个场景，我试图从“UpdateTime”列加载数据，该列仅在对行进行更新时才显式设置（而不是始终设置的 InsertedTime）。对于这种情况，我使用了如下MySqlDataReader.GetMySqlDateTime方法：
using (MySqlDataReader reader = await MySqlHelper.ExecuteReaderAsync(...))
{
if (await reader.ReadAsync())
{
DateTime? updateTime = reader.GetMySqlDateTime("UpdateTime").IsValidDateTime ? (DateTime?)reader["UpdateTime"] : null;
}
}
回答by Manoher Kumar
if "allow zero datetime=true"is not working then use the following sollutions:-
如果“允许零日期时间=真”不起作用，则使用以下解决方案：-
Add this to your connection string: "allow zero datetime=no"- that made the type cast work perfectly.
将此添加到您的连接字符串中： “allow zero datetime=no”- 这使得类型转换工作完美。
回答by Mostafa Asadi
In a Stimulsoft report add this parameter to the connection string (right click on datasource->edit)
在 Stimulsoft 报告中，将此参数添加到连接字符串（右键单击数据源-> 编辑）
Convert Zero Datetime=True;
