Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1142
  • Last Modified:

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.
0
complexodus
Asked:
complexodus
  • 8
  • 6
  • 2
  • +1
1 Solution
 
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
 
gillgatesCommented:
CAST('2000-01-01' AS DATE);

try

DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = CAST('"+ MyTime.TimeOfDay + "')");
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
DarthModCommented:
Submitted to PAQ with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now