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.

Solution 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]);
}

Solution 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.