How to Filter Times in a Dataset

I have a dataset with datetime columns in which there are only h:mm:ss. I would like to filter the dataset where a value would be between to column values.

x > T1 and x < T2

the x value is given in integer, representing a number of seconds.

expression = "x > columnNameT1 and x <  columnNameT2";
DataRow[] foundRows;
foundRows = dt_Tags.Select(expression)
How would expression look like?
Thanks
funnylearningAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
Date/time values are stored as double precision numbers, where the decimal portion indicates the % of a day (12 hours = .5, 6 hours = .25, ...)

so, if you want to pass seconds, you need to convert those to portions of a day.

WHERE [ColumnNameT1] < [X]/60/60/24 AND [ColumnNameT2] > [X]/60/60/24

In this example, [X] is a parameter that you would be asked for when you run your query.
0
funnylearningAuthor Commented:
Hi fyed!
expression = "(" + dMyPos / 60 / 60 / 24 + " > TBeg)  and ( " + dMyPos / 60 / 60 / 24 + " < TEnd ) and ( " + dMyPos / 60 / 60 / 24 + " > 0) and (FilmID = " + myFilm.FilmID.ToString() + ")";
       
equates to:
(0,000814913194444445 > TBeg)  and ( 0,000814913194444445 < TEnd ) and ( 0,000814913194444445 > 0) and (FilmID = 1)

Out of this simple filter I get
Syntax error in expression

Any explanation for that?
0
Dale FyeCommented:
It appears that you are using .Net, I would recommend that you add that zone to your list of zones by clicking the "request attention" hyperlink in the bottom right corner of your original post.

I'm not sure why you need the third expression in this expression.  if dMyPos is a positive value (# of seconds) then the expression: dMyPos/60/60/24 is always going to be greater than zero.

Are TBeg, TEnd, and FilmID all fields in a table?

How are you using this expression in the rest of your code?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

funnylearningAuthor Commented:
Your right about the third expression.
Yes, TBeg, TEnd and FilmID are fields in a table.
             expression = "(" + dMyPos / 60 / 60 / 24 + " > [TBeg])  and ( " + dMyPos / 60 / 60 / 24 + " < [TEnd] ) and ( " + dMyPos / 60 / 60 / 24 + " > 0) and (FilmID = " + myFilm.FilmID.ToString() + ")";
                DataRow[] foundRows;
                foundRows = dt_Tags.Select(expression);
                newitem.Clear();
                foreach (DataRow dr in foundRows)
                {...

Open in new window

0
Dale FyeCommented:
What language is this?

0
funnylearningAuthor Commented:
C#
0
Dale FyeCommented:
You might want to ask the moderators to add the c# zone to your question.  Use the request attention hyperlink in the bottom right corner of your original post.
0
aikimarkCommented:
1. Your regional settings use commas instead of periods.  Try this to see if it is acceptable to the database engine:
(0.000814913194444445 > TBeg)  and ( 0.000814913194444445 < TEnd ) and ( 0.000814913194444445 > 0) and (FilmID = 1)

Open in new window


2. The simpler formula to convert seconds-past-midnight into a time value is to divide by the number of seconds in a day (86400).
0
funnylearningAuthor Commented:
Aikimark, I have a hunch you may be right - but how do I change the culture for just this line of code?
0
aikimarkCommented:
What value displays if you use a .tostring() method to the expression?

You should test my hypothesis with a hard-coded literal expression that uses a period instead of a comma.  Pick any time value expressed in seconds.  Divide that by 86400 and type that into your .Select() expression.  Does it execute?
0
x77Commented:
Gets or sets the expression used to filter rows, calculate the values in a column, or create an aggregate column.

http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx

You can not convert Date to integer or double on a DataColumn expression.
You can not substract dates.
Fuctions for that is too limited.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
funnylearningAuthor Commented:
Thank you.

Although I changed the "," to "." via string replace- that didn't help.

The solution that works is to create an aggregate column that does the division by 86400 in the dataset creation and use that column for value comparison.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

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.