Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

DataTable.Select (against MySQL Time field)

Posted on 2004-08-13

1 solution

Medium Priority

1,107 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

Using IDisposable Part 1: How to use IDisposable objects

Article by: zadeveloper

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…

Log file problem - CircularLogArchiver

Article by: Shaun

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled

How to run like The Flash in Adobe After Effects

Video by: John

This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

Adobe Creative Suite CS

display Triangles! and Circles! in a Microsoft Access Query -- Get Previous Record too

Video by: crystal

Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…