Solved

DataTable.Select (against MySQL Time field)

Posted on 2004-08-13

1 solution

1,082 Views

Last Modified: 2008-03-10

I have a MySQL database with a TIME field (not DateTime, not Date)

After getting the data into a DataTable I want to select records from the DataTable that are at a specific time.

MyTime is a DateTime object

<Example Code>
DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = '"+ MyTime.TimeOfDay +"'");
This produces the error:
"Cannot perform '=' operation on System.TimeSpan and System.String."

So I tried making a TimeSpan object instead like so:
DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = '"+ TimeSpan.Parse(MyTime.TimeOfDay.ToString()) +"'");
This produces the error:
Cannot perform '=' operation on System.TimeSpan and System.String.
</Example Code>

If you have any ideas or solutions please let me know. I hope this isn't a bug.

Comment

Question by:complexodus

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

19 Comments

LVL 15

Expert Comment

by:Thogek

ID: 117965162004-08-13

I don't recall offhand what MySQL's treatment of DateTime types is, but...

Try removing the single-quotes from the filter expression, such as
DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = "+ TimeSpan.Parse(MyTime.TimeOfDay.ToString()));

Or replacing them with pound signs:
DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = #"+ TimeSpan.Parse(MyTime.TimeOfDay.ToString()) +"#");

LVL 15

Expert Comment

by:Thogek

ID: 117965172004-08-13

BTW, is the error you're getting a SqlException, or...?

LVL 3

Expert Comment

by:gillgates

ID: 117966762004-08-13

CAST('2000-01-01' AS DATE);

try

DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = CAST('"+ MyTime.TimeOfDay + "')");

LVL 3

Expert Comment

by:gillgates

ID: 117966812004-08-13

sorry

try

DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = CAST('"+ MyTime.TimeOfDay + "' AS TIME)");

Author Comment

by:complexodus

ID: 117967002004-08-13

When I remove the single-quotes it throws this error.
"Cannot interpret token ':' at position 16."

When I added the # characters it throws this error
"Cannot perform '=' operation on System.TimeSpan and System.DateTime."

And no, its not an SqlExeption (remember I have a DataTable full of the data from MySQL)

Thanks. Good start.

Author Comment

by:complexodus

ID: 117972182004-08-13

I Used:
DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = CAST('"+ MyTime.TimeOfDay + "' AS TIME)");

When I use the CAST() function I get this error.
"The expression contains undefined function call CAST()."

Author Comment

by:complexodus

ID: 117973432004-08-13

Can someone try to post a link to what functions are available inside of DataTable.Select queries and also how the DataTable.Select handles certain strings/formats.

Thanks.

LVL 15

Expert Comment

by:Thogek

ID: 117973802004-08-13

Perhaps....

DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = Convert('"+ MyTime.TimeOfDay + "', System.TimeSpan)");

LVL 15

Expert Comment

by:Thogek

ID: 117973872004-08-13

There's a good discussion of the format of such expression strings in the Remarks section of the DataColumn.Expression Property documentation: http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp

Author Comment

by:complexodus

ID: 117977302004-08-13

I tried converting to System.TimeSpan and it gave me an "invalid conversion" error.

I then tried to convert the TIME column to a string like so....
DataRow[] TodaysAppointments = tblAppointments.Select("CONVERT('Time_Start', System.String) = '"+ MyTime.TimeOfDay + "'");

Though it doesn't throw an error its not finding the row that I know is there.

I also looked at the documentation (http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp) and there doesn't seem to be a handler for TIME fields.

I hope someone could shed some light on this. Thanks for the help.

Author Comment

by:complexodus

ID: 117977672004-08-13

The "invalid conversion" error = "Invalid cast from System.String to System.TimeSpan."

Author Comment

by:complexodus

ID: 117978222004-08-13

Ok... I think this is a bug in DataTable.Select

When I changed my data column type to VARCHAR it found the right rows.

If anyone can pass this on to the .Net developers - they might want to know.

Unless someone out there has a solution I'll consider this quetsion closed.

LVL 15

Expert Comment

by:Thogek

ID: 117986772004-08-13

Have you verified the string values of Time_Start and MyTime.TimeOfDay.ToString() to see if maybe the formats are just different (causing a string comparison to fail)?

It looks as though the string version of a MySQL Time datatype is simply 'hh:mm:ss'.
http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html

To be sure that your MyTime.TimeOfDay TimeSpan instance is using the same format, you could do something like
string strTime = String.Format("{0:hh}:{0:mm}:{0:ss}", MyTime);
DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = '"+ strTime + "'");

If that doesn't work... well... there are clearly some disclarities involved in lining up MySQL Time values and .NET TimeSpan variables for comparison. :-/

Author Comment

by:complexodus

ID: 118012802004-08-14

Yes I have tried to verify the tring compareison...

Thanks

LVL 15

Expert Comment

by:Thogek

ID: 118026352004-08-14

How?

Author Comment

by:complexodus

ID: 120701582004-09-15

Comment from complexodus
Date: 08/13/2004 04:05PM PDT
... Unless someone out there has a solution I'll consider this quetsion closed....

LVL 1

Accepted Solution

by:DarthMod

DarthMod earned 0 total points

ID: 121424902004-09-24

Submitted to PAQ with points refunded (500)

DarthMod
Community Support Moderator

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Datagridview hiding rowheader record selectors

Article by: gpizzuto

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…

Entity Framework and Store Procedures that return multiple resultsets

Article by: Alexandre

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!

Network monitoring: why having a policy is the best policy?

Video by: AdRem

Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…