DataTable Select by exact DateTime

Question

I have a DataTable xData which has a DateTime column with millisecond accuracy, I need to extract the rows which match an exact time with milliseconds but cannot get the syntax correct.

I've tried

DateTime dt = timeStampList[i];
string str = dt.ToString("yyyy-MM-dd HH:mm:ss.fff");
DataRow[] result = xData.Select("TimeStamp2 = " + str);

foreach (DataRow row in result)
{
    Console.WriteLine("{0}, {1}", row[0], row[1]);
}

but

DataRow[] result = xData.Select("TimeStamp2 = " + str);

causes an error:

Syntax error: Missing operand after '16' operator.

I've searched but most of the examples only show how to select by date and not a full datetime with milliseconds.

Answer 1

The problem is that you have to pass the date/time you're looking for to your filter.

At first I thought that sub second precision was not supported by Select but according to Dmitry's answer it is.

Another approach is to use Linq to DataSet which allows you to avoid serializing the date/time:

DateTime dt = timeStampList[i];

IEnumerable<DataRow> selectedRows = 
  xData.AsEnumerable().Where(row => (row.Field<DateTime>("TimeStamp2") == dt));

foreach (DataRow row in selectedRows)
{
  Console.WriteLine("{0}, {1}", row[0], row[1]);
}

Answer 2

If the TimeStamp2 column has the DateTime type, you should surround your DateTime string with the # char:

DataRow[] result = xData.Select("TimeStamp2 = #" + str + "#");

See the Examples section.

If the type of TimeStamp2 column is a string, enclose a value into quotes ':

DataRow[] result = xData.Select("TimeStamp2 = '" + str + "'");

EDIT
A sample code for the DateTime column type:

DataTable table = new DataTable();
table.Columns.Add(new DataColumn("A", typeof (DateTime)));
DateTime dt = new DateTime(2000, 1, 1, 1, 1, 1, 10);
table.Rows.Add(dt);
table.Rows.Add(DateTime.Now);
DataRow[] rows = table.Select("A = #" + dt.ToString("yyyy-MM-dd HH:mm:ss.fff") + "#");

Result: one DataRow in the rows variable.

Answer 3

I too have had problems using the DateTime literal when concerned with the accuracy upto nano seconds. If possible, you can try to use the long datatype in your table and compare DateTime using DateTime.Ticks. This makes the select queries relatively very easy.

Your query would just translate to

DateTime dt = timeStampList[i];
long dtTicks = dt.Ticks;
DataRow[] result = xData.Select("TimeStamp2 = " + dtTicks);

I found this method effectively easy in case of searching between date ranges as well.

If you happen to return the value as a DateTime it can be easily done as

return new DateTime(valueInTicks);