CODEWITHSUNDEEP
c#mysql
Sam
Sam

Reputation: 43

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

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

Upvotes: 1

Views: 10540

Answers (2)

Nischal Tyagi
Nischal Tyagi

Reputation: 199

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"/>

Upvotes: 11

Sam
Sam

Reputation: 43

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.

Upvotes: 0

Related Questions