DataView RowFilter Or DataTable.Select with DateTime conditions in .Net

One needs to be very careful while using datetime in conditions for DataView.RowFilter or DataTable.Select. Because it is not that straight forward as comparing against string to numbers.

The string representation of datetime format depends upon your server/computer's culture or your application's thread culture. While the rowfilter or select method needs the datetime formatted as invariant or en-US culture.

Consider the scenario in which you are running your application running in Spanish culture, the following condition will fail in that case:
dataView.RowFilter = "Date = '" + dateVariable.ToString() + "'";

To overcome this kind of culture specific issues:

  1. Always use Date values enclosed within sharp characters # #
  2. Use culture invariant string as condition. For above mentioned example, the following will always work

dataView.RowFilter = String.Format(CultureInfo.InvariantCulture.DateTimeFormat, "Date = #{0}#", dateVariable);

Loading