Bug #40366 DateTime values returned as 'System.Byte[]'

• Submitted: 28 Oct 2008 8:07
• Modified: 29 Oct 2008 6:54
• Reporter: Maxim Nikolaev
• Status: Not a Bug
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 5.1.7
• OS: Windows
• CPU Architecture: Any
• Tags: datetime

[28 Oct 2008 8:07] Maxim Nikolaev

Description:
Version of MySQL server is 5.0.21; version of connector .NET is 5.1.7 (or later).
Connection string is "DATABASE=db_name;SERVER=localhost;UID=root;PASSWORD=pass;PORT=3307;Pooling=true"
For select queries executed through MySql.Data.MySqlClient.MySqlCommand 
object instance there will be an error: 
Unable to cast object of type 'System.Byte[]' to type 'System.IConvertible', 
if program would try to interpret (convert) some DateTime value returned from query, by next construction:
DateTime dt = Convert.ToDateTime(ESProc.adpProdLoc.SelectCommand.ExecuteScalar());

How to repeat:
This problem occurs looks like only for return from query values, which were included into some expressions inside of query. So “SELECT MAX(DateTimeField) AS MaxDT FROM SampleTable” will produce such problem, but for “SELECT DateTimeField FROM SampleTable” it’ll be ok.

This problem can be handled around, when DateTime will be forced to string type inside of query, for example: SELECT CONVERT(MIN(LastTS), CHAR) AS TS FROM SampleTable. In this case execution of 
"DateTime dt = Convert.ToDateTime(ESProc.adpProdLoc.SelectCommand.ExecuteScalar());"
will be without exception.

[29 Oct 2008 6:54] Tonci Grgin

Hi Maxim and thanks for your report.

Your results are perfectly ok, no matter how they appear to you. Also, you will have this exact problem with *any* connector because it's in MySQL server's metadata, not in code. Using functions like MAX set's BINARY flag (same with ad-hoc queries like "SHOW ...", see Bug#10491). To work around this problem, each connector has an option that assumes result from function to be a string. In c/NET you can turn it on by adding "Functions Return String=true" to your connect string. However, it is only available as of Version 5.2.3 - 8/14/08.

Thanks for your interest in MySQL.

[29 Oct 2008 6:56] Tonci Grgin

See explanation on similar thing in Bug#37485.