= operator doesn't work when matched against DateTime value in RowFilter

[mlawry]

Hi all,

Can anyone explain to me the reason why the following C# code doesn't
work?


DateTime timestamp = DateTime.Today;

DataTable table = new DataTable("My Table");
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Value", typeof(DateTime));
table.Rows.Add(new object[] { 1, timestamp });

table.AcceptChanges();

DataView dv = new DataView(table);
string filter = "Value = #" + timestamp.ToString("u") +
"#";
dv.RowFilter = filter;

// Sample results shown below:
// filter: Value = #2007-01-15 00:00:00Z#
// dv.Count: 0


I'm trying compare against DateTime values in the RowFilter, and (my)
logic says that dv.Count = 1. But the result is always 0. Am I missing
something?

It seems this problem only occur when using the = operator (including

= etc).

Thanks,
Lawry.

 

[Jason Hales]

I tried the same code in VS2003 and VS2005 and it was fine. I even
added a new date and that worked fine:

DateTime timestamp = DateTime.Today;
DateTime lastweek = DateTime.Today.AddDays(-7);


DataTable table = new DataTable("My Table");
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Value", typeof(DateTime));
table.Rows.Add(new object[] { 1, timestamp });
table.Rows.Add(new object[] { 1, lastweek });


table.AcceptChanges();


DataView dv = new DataView(table);
string filter = "Value = #" + timestamp.ToString("u") +
"#";
dv.RowFilter = filter;

Hi all,

Can anyone explain to me the reason why the following C# code doesn't
work?

DateTime timestamp = DateTime.Today;

DataTable table = new DataTable("My Table");
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Value", typeof(DateTime));
table.Rows.Add(new object[] { 1, timestamp });

table.AcceptChanges();

DataView dv = new DataView(table);
string filter = "Value = #" + timestamp.ToString("u") +
"#";
dv.RowFilter = filter;

// Sample results shown below:
// filter: Value = #2007-01-15 00:00:00Z#
// dv.Count: 0

I'm trying compare against DateTime values in the RowFilter, and (my)
logic says that dv.Count = 1. But the result is always 0. Am I missing
something?

It seems this problem only occur when using the = operator (including

= etc).Thanks,

Lawry.

 

[mlawry]

What do you mean by "worked fine"? Of course, the code works perfectly
(it should). It's the value of

dv.Count

after applying the
RowFilter that I'm concerned with.

What value do you get when you print dv.Count after setting the
dv.RowFilter property?

Lawry.

 

[Amar]

Hi,
If you just want to compare against the datetime column you can
directly use the following code..

string filter = "Value = '" + timestamp + "'";

instead of using converting the date into string format.
This works as desired.
Hope this helps..

 

[mlawry]

Hi, thanks for all your help so far, but I think I hit the bottom of
the problem. (I do hope that is the case.)

First of all, there is the bug:
http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=95799

Apparently the code used to compare DateTime are culture dependent
(fair enough). However it is interesting to note that the sample code
in the link above (re-pasted below) actually raises an exception:

try {
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add("Customers");
dt.Locale = new CultureInfo("en-GB");
dt.Columns.Add("CustId", typeof(int));
dt.Columns.Add("OrderDate", typeof(DateTime));
DateTime dateTime = new DateTime(2004, 1, 20);
dt.Rows.Add(new object[] { 1, dateTime });

DataView dv = dt.DefaultView;
string s = String.Format(new
CultureInfo("en-GB").DateTimeFormat, "OrderDate = #{0:d}#", dateTime);
Console.WriteLine(s);
dv.RowFilter = s; // EXCEPTION RAISED HERE
Console.WriteLine(dv.Count);
} catch (Exception exc) {
Console.WriteLine(exc);
}

The exception raised is (when run in .NET 2.0).

System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi,
DateTimeStyles styles)
at System.DateTime.Parse(String s, IFormatProvider provider)
at System.Data.ConstNode..ctor(DataTable table, ValueType type,
Object constant, Boolean fParseQuotes)
at System.Data.ExpressionParser.Parse()
at System.Data.DataExpression..ctor(DataTable table, String
expression, Type type)
at System.Data.DataView.set_RowFilter(String value)

Now I don't know why the exception occurs, but I'll leave it up to them
Microsoft guys to work it out.

Amar's suggestion seems to be the only workable solution to me (at
least for now). However, there is a catch to using single quotes (')
instead of hashes (#) to surround the date:

The DateTime string has to be formated using the locale of the
DataTable.

What lead me to this is the fact that our application software used
single quotes to specify DateTime in RowFilter string, and this has
worked fine in .NET 1.1. But recently we tested it on .NET 2.0 and it
is broken, receiving a System.Data.EvaluateException:

System.Data.EvaluateException: Cannot perform '=' operation on
System.DateTime and System.String.

After realising everything is (and should be) culture sensitive, it
turned out the problem was that the DataTable.Locale property was en-US
while the DateTime was formatted with en-AU. So what Amar says is
correct, provided you format the DateTime with the CultureInfo from
DateTime.Locale.

Hopefully this will be all.

Lawry.