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!

Freud

Answer 1

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

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

Goode

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! – Freud

Didn't know about the date format matching system settings. Thanks for educating me and anyone else who reads this! – Goode

@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? – Infract

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. – Freud

Answer 2

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") +
         "# ) ";

Ciliary

Answer 3

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.

Lambda