Using BETWEEN in a DataTable.Select

Question

I thought this would be simple but obviously not!

Basically, I have a date and want to do a 'between' on two date columns like this:

myDataTable.Select(myDate & " between (StartDate and EndDate)")

Where StartDate and EndDate are date columns that do exist in the DataTable.

Any ideas?

Solution 1

Why not just use >= and <=

Dim dt As New DataTable()
dt.Columns.Add("StartDate")
dt.Columns.Add("EndDate")
Dim row1 As DataRow = dt.NewRow()
row1("StartDate") = New DateTime(2009, 1, 1)
row1("EndDate") = New DateTime(2009, 1, 31)
dt.Rows.Add(row1)

Dim myDate As New DateTime(2008, 12, 15)
Dim rows As DataRow() = dt.[Select]([String].Format("#{0}# >= StartDate AND #{0}# <= EndDate", myDate.ToString("dd MMM yyyy")))

Solution 2

The DataTable.Select method doesn't support the BETWEEN operation. This operation is specific to a database engine. Remember that the DataTable is an in-memory construct and doesn't necessarily support all the features of a database server.

The DataTable.Select method supports the same filter expression syntax as DataColumn.Expression. You can try the following expression to achieve the same thing (note I haven't tested this!):

myDataTable.Select("#" + myDate + "# >= StartDate AND EndDate <= #" + myDate + "#");