DefaultView.RowFilter throws Format Exception with different DateTime formats

Question

I must have a culture which has date format like this dd.mm.yyyy , but when i forward it to row filter like this:

myTable.DefaultView.RowFilter="opened_on=#"+datetimepicker1.value.ToShortDateString()+"#"

It says that input string is not in valid DateTime format.
When user has culture with dd/mm/yyyy format without . it works perfectly.
Anybody knows why?

Answer 1

When you want to filter rows of a DataTable using the DefaultView.RowFilter property on a DateTime column, you should always use the general format of the invariant culture. (MM/dd/yyyy) as explained in MSDN for the Expression property from the DataColumn class

All literal expressions must be expressed in the invariant culture locale. When DataSet parses and converts literal expressions, it always uses the invariant culture, not the current culture.

This could easily verified with something like this

DateTime t = new DateTime(2018,12,9);

// will find rows with date = September, 12 2018 
// or will fail if your date is 13/12/2018
// string fmt = t.ToShortDateString();


string fmt = t.ToString("MM.dd.yyyy");
dt.DefaultView.RowFilter = "ADateColumn = #" + fmt + "#";