DataTable.Select Behaves Strangely Using ISNULL Operator on NULL DateTime Column

Question

I have a DataTable with a DateTime column, "DateCol", that can be DBNull. The DataTable has one row in it with a NULL value in this column.

I am trying to query rows that have either DBNull value in this column or a date that is greater than today's date. Today's date is 5/11/2010. I built a query to select the rows I want, but it did not work as expected. The query was:

string query = "ISNULL(DateCol, '" + DateTime.MaxValue + "'") > "' + DateTime.Today "'"

This results in the following query:

"ISNULL(DateCol, '12/31/9999 11:59:59 PM') > '5/11/2010'"

When I run this query, I get no results. It took me a while to figure out why. What follows is my investigation in the Visual Studio immediate window:

> dt.Rows.Count
1
> dt.Rows[0]["DateCol"]
{}
> dt.Rows[0]["DateCol"] == DBNull.Value
true
> dt.Select("ISNULL(DateCol,'12/31/9999 11:59:59 PM') > '5/11/2010'").Length
0  <-- I expected 1

Trial and error showed a difference in the date checks at the following boundary:

> dt.Select("ISNULL(DateCol, '12/31/9999 11:59:59 PM') > '2/1/2000'").Length
0
> dt.Select("ISNULL(DateCol, '12/31/9999 11:59:59 PM') > '1/31/2000'").Length
1 <-- this was the expected answer

The query works fine if I wrap the DateTime field in # instead of quotes.

> dt.Select("ISNULL(DateCol, #12/31/9999#) > #5/11/2010#").Length
1

My machine's regional settings is currently set to EN-US, and the short date format is M/d/yyyy.

Why did the original query return the wrong results?

Why would it work fine if the date was compared against 1/31/2000 but not against 2/1/2000?

Answer 1

The query expression format uses #...# for DateTime values. Single quotes are used for string values. In the expressions where you are using single quotes around the DateTime, it is doing a string comparison, in which the character "1" in "12/31/9999" comes before the "5" in "5/11/2010" and the "2" in "2/1/2000", but not the "1" in "1/31/2000", in Unicode order.