Solved

DataTable.Select (against MySQL Time field)

Posted on 2004-08-13
19
1,016 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
Comment Utility
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
Comment Utility
BTW, is the error you're getting a SqlException, or...?
0
 
LVL 3

Expert Comment

by:gillgates
Comment Utility
CAST('2000-01-01' AS DATE);

try

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

Expert Comment

by:gillgates
Comment Utility
sorry

try

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

Author Comment

by:complexodus
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Perhaps....

    DataRow[] TodaysAppointments = tblAppointments.Select("Time_Start = Convert('"+ MyTime.TimeOfDay + "', System.TimeSpan)");
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 15

Expert Comment

by:Thogek
Comment Utility
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
Comment Utility
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
Comment Utility
The "invalid conversion" error = "Invalid cast from System.String to System.TimeSpan."
0
 

Author Comment

by:complexodus
Comment Utility
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
Comment Utility
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
Comment Utility
Yes I have tried to verify the tring compareison...

Thanks
0
 
LVL 15

Expert Comment

by:Thogek
Comment Utility
How?
0
 

Author Comment

by:complexodus
Comment Utility
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
Comment Utility
Submitted to PAQ with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now