Compare dates in DataView.RowFilter?

Question

I am scratching my head over something rather stupid yet apparently difficult.

DataView dvFormula = dsFormula.Tables[0].DefaultView;
dvFormula.RowFilter = "'" + startDate.ToString("yyyyMMdd") + "' < EndDate OR EndDate = '19000101'";
dvFormula.Sort = "FromDate ASC";

The result is this:

Cannot perform '<' operation on System.String and System.DateTime.

Please tell me what the best way to solve this problem would be.

Much appreciated!

score 27 · Accepted Answer · edited Sep 15 '16 at 07:46

27

You need to wrap your dates with #, not apostrophes.

dvFormula.RowFilter = "#" + startDate.ToString("MM/dd/yyyy") + "# < EndDate OR EndDate = #1/1/1900#";

edited Sep 15 '16 at 07:46

sashoalm

75,001
122
434
781

answered Aug 27 '10 at 13:28

Dan

1,176
1
9
10

4

You're right it was the #. But the crucial part is that the dateformat needs to be the same as the one your system settings are using. How stupid is that... so now I'm using `startDate.ToShortDateString()`. Thanks m8! – Peter Aug 27 '10 at 13:36
1

Didn't know about the date format matching system settings. Thanks for educating me and anyone else who reads this! – Dan Aug 27 '10 at 15:47
6

@Peter, here in the UK (where our date format makes sense - dd/mm/yyyy) I have to use EITHER "yyyy/MM/dd" or the American layout. If I do ToShortDateString then it has a flip-out. In addition, I recommend the "yyyy/MM/dd" format as it is not a locale specific format - unless anybody has better info? – noelicus Nov 15 '12 at 16:42
1

In T-SQL I'm used to the `yyyyMMdd` format but with rowfilter `yyyy/MM/dd` looks like the way to go then? Thanks, very helpfull. – Peter Nov 16 '12 at 08:20

score 12 · Answer 2 · edited Oct 28 '11 at 23:07

12

This is the solution. Try this:

filter = " (Date >= #" +
         Convert.ToDateTime(txtFromDate.Text).ToString("MM/dd/yyyy") +
         "# And Date <= #" +
         Convert.ToDateTime(txtToDate.Text).ToString("MM/dd/yyyy") +
         "# ) ";

edited Oct 28 '11 at 23:07

LPL

16,827
6
51
95

answered May 25 '11 at 12:56

Darsh

121
1
2

score 5 · Answer 3 · answered Aug 27 '10 at 13:27

5

Depending on your data provider, you may need to escape dates with the # character rather than the ' character. In addition, I would format your dates in the format YYYY-MM-DD to ensure it can be recognized as a date correctly.

answered Aug 27 '10 at 13:27

Ryan Brunner

14,723
1
36
52

Compare dates in DataView.RowFilter?

3 Answers3

Linked