Filter a DataTable using DateTime value

Question

Updated:

I am trying to filter a DataTable based on a particular date. My datatable has a column "whn" which contains dates. A Sample date from the DataTable:

{21/02/2012 10:03:53}   object {System.DateTime}

Here is the code I am using to try and filter the DataTable:

 String datevalue= "21/02/2012 10:03:53";

  DataRow[] foundRows;
  foundRows = dttemp.Select(String.Format("whn = '{0}'", datevalue));

However this does not work and returns 0 rows. Even though I know that a row containing the date in "datevalue" exists.

Unsure why this is not working, any help is appreciated.

Thanks.

score 7 · Accepted Answer · edited May 23 '17 at 12:06

If you want to exactly match the supplied DateTime, including fractions of a second, you should use a DateTime format that has enough precision. Probably the round-trip format ("o") is a good bet:

foundRows = dttemp.Select(String.Format(dttemp.Locale, "whn = '{0:o}'", datevalue));

However, it's more likely you want to match values that fall into a range. For example, if you want all values that have the same date, but any time of day, you could use:

foundRows = dttemp.Select(String.Format(dttemp.Locale, "whn >='{0:o}' AND whn < '{1:o}'",
      datevalue.Date, datevalue.AddDays(1).Date));

Similarly if you want all values that are in the same second (but may have fractions of a second), you can use:

DateTime from = dttemp.AddTicks( - (dttemp.Ticks % TimeSpan.TicksPerSecond));
foundRows = dttemp.Select(String.Format(dttemp.Locale, "whn >='{0:o}' AND whn < '{1:o}'",
      from, from.AddSeconds(1)));

The call to AddTicks truncates the supplied DateTime to a whole number of seconds, as described in the accepted answer to this StackOverflow question.

Note I used dttemp.Locale to use the correct locale (CultureInfo) in case your DataTable has a locale other than your current culture.

Thanks, really helpful response. Getting an error though : `A first chance exception of type 'System.ArgumentOutOfRangeException' occurred in mscorlib.dll` — Jonathon Fry, Nov 21 '12 at 19:21
Ah nevermind, managed to get it working. Thanks for the help! Fixed my problem by parsing my string to DateTime. `DateTime test_time = DateTime.Parse(datevalue);` — Jonathon Fry, Nov 21 '12 at 19:26

enz0 · Answer 2 · 2012-11-21T18:32:45.657

2

foundRows = dttemp.Select("whn LIKE '{0}'",datevalue);

should probably be

foundRows = dttemp.Select(String.Format("whn LIKE '{0}'",datevalue));

more info here http://www.csharp-examples.net/dataview-rowfilter/

edited Nov 21 '12 at 18:32

answered Nov 21 '12 at 18:08

enz0

131
5

Thanks, that gets rid of the first change exception error. However it returns 0 rows even though I know there is a row that has the specified date. Any ideas ? – Jonathon Fry Nov 21 '12 at 18:15

score 1 · Answer 3 · answered Nov 21 '12 at 18:17

1

Use = instead of LIKE

 foundRows = dttemp.Select(String.Format("whn = '{0}'", datevalue));

answered Nov 21 '12 at 18:17

codingbiz

26,179
8
59
96

Yeah I did change to = but still returns 0 rows. Not sure why. The "whn" column is formatted as DateTime. Does this affect it? – Jonathon Fry Nov 21 '12 at 18:20
if you're searching a datetime column you have to surround the value with # so something like: foundRows = dttemp.Select(String.Format("whn = #{0}#", datevalue)); – enz0 Nov 21 '12 at 18:28
No it does not affect it. But if time portion are compared they may not match - the time difference – codingbiz Nov 21 '12 at 18:29
This won't work for values in the DataTable that have fractions of a second - the default format ("g") that you're using doesn't include fractions of a second. See my answer. – Joe Nov 21 '12 at 18:42

Filter a DataTable using DateTime value

3 Answers3