DataTable.Select (against MySQL Time field)

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.
complexodusAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
DarthModConnect With a Mentor Commented:
Submitted to PAQ with points refunded (500)

DarthMod
Community Support Moderator
0
 
ThogekCommented:
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()) +"#");
0
 
ThogekCommented:
BTW, is the error you're getting a SqlException, or...?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Go Premium
 
gillgatesCommented:
CAST('2000-01-01' AS DATE);

try

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

try

DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = CAST('"+ MyTime.TimeOfDay + "' AS TIME)");
0
 
complexodusAuthor Commented:
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.
0
 
complexodusAuthor Commented:
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()."
0
 
complexodusAuthor Commented:
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.
0
 
ThogekCommented:
Perhaps....

    DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = Convert('"+ MyTime.TimeOfDay + "', System.TimeSpan)");
0
 
ThogekCommented:
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
0
 
complexodusAuthor Commented:
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.
0
 
complexodusAuthor Commented:
The "invalid conversion" error = "Invalid cast from System.String to System.TimeSpan."
0
 
complexodusAuthor Commented:
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.
0
 
ThogekCommented:
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.  :-/
0
 
complexodusAuthor Commented:
Yes I have tried to verify the tring compareison...

Thanks
0
 
ThogekCommented:
How?
0
 
complexodusAuthor Commented:
Comment from complexodus
Date: 08/13/2004 04:05PM PDT
... Unless someone out there has a solution I'll consider this quetsion closed....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.