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

Question

I get this error:

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

while I am trying to fetch the data from a MySQL database. I have the date datatype in my MySQL database. But while retrieving it into my datatable, it get the error above.

How can I fix this?

Possible duplicate of [Unable to convert MySQL date/time value to System.DateTime](http://stackoverflow.com/questions/2934844/unable-to-convert-mysql-date-time-value-to-system-datetime) — Gopi, Oct 13 '16 at 12:54

score 230 · Answer 1 · edited Jun 27 '12 at 08:49

230

You must add Convert Zero Datetime=True to your connection string, for example:

server=localhost;User Id=root;password=mautauaja;Persist Security Info=True;database=test;Convert Zero Datetime=True

edited Jun 27 '12 at 08:49

Marc Mutz - mmutz

24,485
12
80
90

answered Jun 27 '12 at 08:03

agni

2,301
2
13
2

4

Thanks! FYI: This goes on the MySQL connection string - not the SQL Server connection string. – Jun 19 '14 at 22:01
saved my time. Worked like a charm – Naila Akbar Mar 22 '17 at 11:10
+1 - Perfect! I had zero datetimes because when the column wasn't fill write on table MySQL the zero datetime. I prefer change it from now for 0970-01-01.Thanks you so much – Drako Aug 22 '18 at 12:23

score 52 · Accepted Answer · answered Apr 22 '11 at 18:25

52

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?

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.

Reference: http://bugs.mysql.com/bug.php?id=26054

answered Apr 22 '11 at 18:25

dkretz

37,399
13
80
138

2

I suggest not to use '0000-00-00 00:00:00' as your data in date/time columns. Rather use a real date values in your data - if you are using .net. However, MySQL can handle any dates. You should scan through all your date/time values. – Bimal Poudel Sep 14 '16 at 15:27

score 23 · Answer 3 · edited Mar 01 '16 at 05:47

23

i added both Convert Zero Datetime=True & Allow Zero Datetime=True and it works fine

edited Mar 01 '16 at 05:47

urfusion

5,528
5
50
87

answered Jul 11 '12 at 21:16

Kiddo

5,052
6
47
69

score 3 · Answer 4 · answered May 12 '14 at 11:41

3

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.

answered May 12 '14 at 11:41

Singaravelan

809
3
19
32

score 3 · Answer 5 · answered Apr 22 '11 at 17:55

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.

Check here more info on the DateTime.ParseExact

score 2 · Answer 6 · answered Apr 22 '11 at 11:16

2

Let MySql convert your unix timestamp to string. Use the mysql function FROM_UNIXTIME( 113283901 )

answered Apr 22 '11 at 11:16

Jakob Alexander Eichler

2,988
3
33
49

P Walker · Answer 7 · 2018-09-22T05:16:49.253

Rather than changing the connection string, you can use the IsValidDateTime property of the MySqlDateTime object to help you determine if you can cast the object as a 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.GetMySqlDateTime method like so:

using (MySqlDataReader reader = await MySqlHelper.ExecuteReaderAsync(...))
{
    if (await reader.ReadAsync())
    {
        DateTime? updateTime = reader.GetMySqlDateTime("UpdateTime").IsValidDateTime ? (DateTime?)reader["UpdateTime"] : null;
    }
}

score 1 · Answer 8 · edited Jul 04 '19 at 21:23

1

In a Stimulsoft report add this parameter to the connection string (right click on datasource->edit)

Convert Zero Datetime=True;

edited Jul 04 '19 at 21:23

sedders123

791
1
9
19

answered Jul 04 '19 at 18:46

Mostafa Asadi

339
4
8

score 1 · Answer 9 · answered May 06 '21 at 00:22

When you're using EF edmx with MySql, please review the Precision attribute in the entitytype.

<EntityType Name="table">
  <Key>
    <PropertyRef Name="Id" />
  </Key>
  <Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
  <Property Name="date_field" Type="datetime" Precision="0" Nullable="false" />
</EntityType>

If you made changes in the datetime field, it could be possible you're missing this attribute in the property.

score 0 · Answer 10 · edited Nov 19 '16 at 19:15

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

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

score 0 · Answer 11 · answered Feb 21 '19 at 17:27

0

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.

answered Feb 21 '19 at 17:27

Manoher Kumar

279
1
3
10

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

11 Answers11

Linked

Related