Solved

DataTable.Select (against MySQL Time field)

Posted on 2004-08-13
19
1,054 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.
0
Comment
Question by:complexodus
  • 8
  • 6
  • 2
  • +1
19 Comments
 
LVL 15

Expert Comment

by:Thogek
ID: 11796516
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
 
LVL 15

Expert Comment

by:Thogek
ID: 11796517
BTW, is the error you're getting a SqlException, or...?
0
 
LVL 3

Expert Comment

by:gillgates
ID: 11796676
CAST('2000-01-01' AS DATE);

try

DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = CAST('"+ MyTime.TimeOfDay + "')");
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 3

Expert Comment

by:gillgates
ID: 11796681
sorry

try

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

Author Comment

by:complexodus
ID: 11796700
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
 

Author Comment

by:complexodus
ID: 11797218
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
 

Author Comment

by:complexodus
ID: 11797343
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
 
LVL 15

Expert Comment

by:Thogek
ID: 11797380
Perhaps....

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

Expert Comment

by:Thogek
ID: 11797387
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
 

Author Comment

by:complexodus
ID: 11797730
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
 

Author Comment

by:complexodus
ID: 11797767
The "invalid conversion" error = "Invalid cast from System.String to System.TimeSpan."
0
 

Author Comment

by:complexodus
ID: 11797822
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
 
LVL 15

Expert Comment

by:Thogek
ID: 11798677
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
 

Author Comment

by:complexodus
ID: 11801280
Yes I have tried to verify the tring compareison...

Thanks
0
 
LVL 15

Expert Comment

by:Thogek
ID: 11802635
How?
0
 

Author Comment

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

Accepted Solution

by:
DarthMod earned 0 total points
ID: 12142490
Submitted to PAQ with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question