RowFilter for a DateTime column

[Kurt]

Hi,

 

I have a DataSet with a DataTable of the folowing format (fields):

 

timestamp, value

 

I would like to find 1 specific value in the DataTable, based on a timestamp the user provides.

For example: 17th august 2004, 23:00

 

I tried some different things like;

 

dataSet.dataTable.DefaultView.RowFilter = "timestamp = '2004/08/17 23:00:00'"

 

but then the Count property of the DefaultView becomes 0!

 

The DataTable contains the value though, and the RowFilter expression works fine when I use it in the WHERE clause of a T-SQL statement and request the data directly from the database...

 

What am I doing wrong? Is there a way to specify a DateTime value to search on?

[Nerseus]

I think by "timestamp" you mean a Date/Time column, right? A timestamp in SQL Server is a special type of column that doesn't really contain a date/time, but just a time "stamp" - a hex/binary value.

 

If you really mean a Date/Time value, then your filter should work just fine. I tried various tests and they all seemed to work. For example, I used:

'8/17/2004 11pm'

'8/17/2004 23:00:00'

'2004/8/17 11pm'

'2004/08/17 11pm'

'2004/08/17 23:00:00'

 

It's possible that your local machine and the SQL Server on a different timezone. If so, your time portion may be off by an hour or more from what you think it is... or maybe it's not right on the hour, but has some minutes, seconds, etc. in it.

 

-ner

[Kurt]

Yes, it works for me in the WHERE clause of a query ran against the SQL server. But what I have is a DataTable object filled with rows from the SQL table.

 

let's say the content of the System.Data.DataSet.DataTable is;

 

timestamp(DateTime)     value(int)
2004/02/12 20:00:00     4
2004/02/12 21:00:00     8
2004/02/12 22:00:00     9

 

 

What I would like to do is for example;

 

myDataSet.myDataTable.DefaultView.RowFilter = "timestamp = '2004/02/12 21:00:00'";

 

But then the value of;

 

myDataSet.myDataTable.DefaultView.Count

 

becomes 0!

 

I could solve this for example like;

 

int value = 0;
bool found = false;
foreach (myDataTableRow row in myDataTable)
{
   if (row.timestamp == new System.DateTime(2004,2,12,21,0,0))
   {
       value = row.value;
       found = true;
       break;
   }
}

 

But I 'm just looking for a way to work more directly by using the RowFilter property. Hope you can help me... I guess it has something to do how the XML DataSet represents the DateTime values internally (= as a string with timezone included). In that case it's probably a mather of making a methode that produces a string of that format when given a DateTime object. The solution I provided doesn't have the problem, the CLR knows that the timestamp field from the typed DataSet.DataTable should be casted to a System.DateTime object when referenced in the source code...

[ost]

hmm... have you tried to use the select method of the datatable instead of rowfilter?

 

Maybe that works out better for you ;)

 

 

next statement is in Danish ;)

 

Rart at se en anden dansker her på forummet..... :D

[Diesel]

ost, you are funny

[Kurt]

Ost, jeg er faktisk ikke dansker, men belgier... Bor i Denmark for 2 år nu