System.Data.SQLite

Hey Robert,

I have a simple request: could you change the SQLiteConvert.ToDateTime(string) from this:

switch (_datetimeFormat)
{
  case SQLiteDateFormats.Ticks:
    return new DateTime(Convert.ToInt64(dateText, CultureInfo.InvariantCulture));
  case SQLiteDateFormats.JulianDay:
    return ToDateTime(Convert.ToDouble(dateText, CultureInfo.InvariantCulture));
  default:
    return DateTime.ParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None);
}

to something like this:

switch (_datetimeFormat)
{
  case SQLiteDateFormats.Ticks:
    return new DateTime(Convert.ToInt64(dateText, CultureInfo.InvariantCulture));
  case SQLiteDateFormats.JulianDay:
    return ToDateTime(Convert.ToDouble(dateText, CultureInfo.InvariantCulture));
  default:
    {
      DateTime convertedDate = DateTime.MinValue;
      DateTime.TryParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None, out convertedDate);
      return convertedDate;
    }
}

I ended up making this change for a project I am working on that uses data ported from MySQL. The client is using the empty date (0000-00-00 00:00:00) instead of NULL. The empty date was causing ParseExact to fail with FormatException (not a valid datetime); TryParseExact is a nice replacement because it will return DateTime.MinValue if the conversion fails.

The problem is that TryParseExact() doesn't exist on the Compact Framework.

Okay, so how about somthing like this then (below). The point is to handle non-datetime values better, imo the ToDateTime() should return a System.DateTime, not throw an exception if the value being parsed is crappy:

DateTime convertedDate = DateTime.MinValue;
try
{
  convertedDate = DateTime.ParseExact(dateText, _datetimeFormats, DateTimeFormatInfo.InvariantInfo, DateTimeStyles.None);
}
catch
{
}
return convertedDate;

While that works for your use-case scenario, there's a lot of other scenarios that I can see where this kind of behavior would be very undesirable.

I'd rather throw an error than return invalid or incorrect information back to the user.  If I implement this, I could have a slew of other users posting angry "how come your provider returns the wrong date?" messages on the forums when their improperly-formatted dates start returning an entirely different date than the one they inserted into the database.