不使用日期时“无法将 MySQL 日期/时间值转换为 System.DateTime" - C#.Net [英] 'Unable to convert MySQL date/time value to System.DateTime' WHEN not using a date - C#.Net

问题描述

我有这个查询:

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

serial 列是一个 varchar 字段.这里没有日期/时间.也许 .Net 或 MySQL 认为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?

我收到此错误:

MySql.Data.Types.MySqlConversionException: '无法将 MySQL 日期/时间值转换为 System.DateTime'

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

奇怪的是，此查询在 MySql 查询浏览器中运行良好，但在我的 C# 应用程序中运行时抛出该异常.

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

我试过了:

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

为了见鬼去，我把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...

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

也行不通.我不认为它与 C# 代码有关，因为它只是一个查询，它是一个 MySQL 异常，但这里是使用的代码:

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 + "%';");

调用的是这个函数:

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;
}

编辑 2:

使用以下序列号:19-111115-11、19-111116-11、19-111118-11 都有效……只是 19-111117-11 无效.似乎与以下有关:

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 <-那个数字

19-111117-11 <- that digit

推荐答案

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

不起作用但是:

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

作品.

这篇关于不使用日期时“无法将 MySQL 日期/时间值转换为 System.DateTime" - C#.Net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！