C# ODBCDataReader reading null data and null date values

Question

I'm reading a record from a DB2 database via ODBC connection. The data is populated into an ODBCDataReader. As I'm going through my foreach loop I'm running into problems trying to parse the multiple different DateTime fields.

Some of the fields are null, some have null date time value (9999-12-31 24:00:00.000000) and some have valid date time values (2010-07-09 20:43:32.037234).

I've tried doing something like this to catch null date time errors:

if (!dr[dbFieldName].Equals(DBNull.Value))
{
  if (dr.GetDate(dr.GetOrdinal(dbFieldName)).Equals(DateTime.Parse("9999-12-31 24:00:00.000000")))
  {
    fieldValues[tag] = "";
  }
  else
  {
    strValue = dr.GetDate(dr.GetOrdinal(dbFieldName)).ToString("s");
    fieldValues[tag] = strValue.Trim();
  }
}

The GetType().Name != "DBNull" seems to work for catching null values. However the next if statement throws an ArgumentOutOfRangeException error. This appears to happen on fields with the 9999-12-31 24:00:00.000000 values.

Is there a way to properly parse this? It seems like any way I try to evaluate these null date time fields a error is thrown.

Answer 1

Two suggestions:

1) Use the DBNull.Value object to check for null.
2) Use DateTime.MaxValue to check 12/31/999 24:00:00.

Hopefully that will help you get closer to finding the problem.

Answer 2

In addition to Jerod's comment.

DateTime.Parse("9999-12-31 23:59:00.000000") is valid
DateTime.Parse("9999-12-31 24:00:00.000000") is invalid

update....

The problem is 24:00 is not considered a valid time in the 24 hour clock (regardless of date). You may be able to work around that by using

    DateTime.ParseExact("9999-12-31 24:00:00.000000", "yyyy-MM-dd 24:mm:ss.ffffff",
System.Globalization.CultureInfo.InvariantCulture); 

after a DateTime.TryParse method. If TryParse succeeds, you have a valid date. If it returns false, the above statemen tis executed.

Answer 3

Wrap the IF/ELSE in a TRY/CATCH. It's not considered good form, but catching the exception is gauranteed to work.

Answer 4

What about DateTime.TryParse?

Answer 5

Try this:

Date dt;

// DBNull.Value.ToString() results in an empty string

if (Date.TryParse(dr[dbFieldName].ToString(), dt)) {
fieldValues[tag] = dt.ToString("s");
} else {
fieldValues[tag] = "";
}