DataTable.Select Behaves Strangely Using ISNULL Operator on NULL DateTime Column
- by Paul Williams
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?
