• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 530
  • Last Modified:

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
0
funnylearning
Asked:
funnylearning
  • 5
  • 4
  • 2
  • +1
1 Solution
 
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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
 
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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