'Unable to convert MySQL date/time value to System.DateTime' WHEN not using a date - C#.Net

Question

I have this query:

SELECT * FROM records WHERE serial LIKE '%19-111117-11%';

the serial column is a varchar field. There is no date/time in here. Maybe .Net or MySQL is thinking the '19-111117-11' is a date?

I get this error:

MySql.Data.Types.MySqlConversionException: 'Unable to convert MySQL date/time value to System.DateTime'

The odd thing is this query works fine in MySql Query Browser but throws that exception when running in my C# application.

I've tried:

SELECT * FROM records WHERE CAST(serial as char) LIKE '%19-111117-11%';

And just for the hell of it, I broke the '19-111117-11' into two parts like this:

(kinda pseudo code, since I tried more things after)

string p1 = serial.substring(0, serial.length/2)
string p2 = serial.substring(serial.length)

string query = "SELECT * FROM records WHERE SUBSTRING(serial,0,LENGTH(serial)/2) = " + p1 + "' AND  SUBSTRING(serial,LENGTH(serial)/2,LENGTH(serial)/2) = '" + p2 + "';";

Please help! Such an annoying issue. I'm not sure why I get a convert to date/time exception. I'm not using dates at all...

EDIT:

SELECT * FROM records WHERE `serial` LIKE '%19-111117-11%';

Also, does not work. I do not think it's related to C# code, as it's only a query and it's a MySQL exception, but here is the code used:

string serial = "19-111117-11";
DataTable tracking_info = MySqlGetDataTable("SELECT * FROM records WHERE `serial` LIKE '%" + serial + "%';");

And the call is to this function:

public static DataTable MySqlGetDataTable(string query, string connString = "default")
{       
    var data = new DataTable();
    using (var conn = new MySqlConnection(connString == "default" ? Program.CidDataConnectionString : connString))
    {
        var adapter = new MySqlDataAdapter(query, conn);
        adapter.Fill(data);
    }

    return data;
}

EDIT 2:

Using a serial number of: 19-111115-11, 19-111116-11, 19-111118-11 all work...it's just 19-111117-11 that's not working. It seems to be related to:

19-111117-11 <- that digit

Sounds like the issue is in your C# code, post that and we can take a look — Brad, Feb 20 '19 at 18:14
`serial` seems to be a keyword in MySql. Try surrounding that identifier with backticks `\`serial\`` — Chris Dunaway, Feb 20 '19 at 20:16
Thanks for the answers. I edited the post. Still not working — Sam, Feb 22 '19 at 14:41
[`select *` is almost always a bad idea.](https://stackoverflow.com/q/3639861/215552) — Heretic Monkey, Feb 22 '19 at 15:05
Sorry, @HereticMonkey. You were correct. I don't understand why select * does not work but changing it to get a specific column corrected the issue. — Sam, Feb 22 '19 at 16:06

score 11 · Answer 1 · answered Mar 19 '19 at 06:48

11

Set 'Zero Datetime=True' in app config file

<add name="CONNECTIONSTRINGNAME" connectionString="server's HOSTNAMEorIP(i.e Localhost or IP address);user id=USER_ID;Pwd=PASSWORD;persistsecurityinfo=True;database=DATABASENAME;Convert Zero Datetime=True" providerName="MySql.Data.MySqlClient"/>

answered Mar 19 '19 at 06:48

Nischal Tyagi

199
1
10

For me this worked when I had a date-column in the database, where a default value was "0000-00-00". – Sundancer Jun 11 '22 at 18:06

score 0 · Accepted Answer · answered Feb 22 '19 at 16:07

0

SELECT * FROM records WHERE `serial` LIKE '%19-111117-11%';

Does not work but:

SELECT col1, col2, col3... FROM records WHERE `serial` LIKE '%19-111117-11%';

Works.

answered Feb 22 '19 at 16:07

Sam

43
1
6

'Unable to convert MySQL date/time value to System.DateTime' WHEN not using a date - C#.Net

2 Answers2

Linked