System.Data.SQLite

I have a sqlite table that has a DATE column (I am 100% positive that all of the values are valid).  When I try to do a .Fill into a data table, I get the exception "String was not recognized as a valid DateTime."

   at System.DateTimeParse.ParseExactMultiple(String s, String[ formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at System.DateTime.ParseExact(String s, String[ formats, IFormatProvider provider, DateTimeStyles style)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, Int32 index, SQLiteType typ)
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at System.Data.SQLite.SQLiteDataReader.GetValues(Object[ values)

I would expect this to not error out and the result to be DBNull in that particular row/column, am I mistaken?

I am using VS2010 B2 and am using the build that for this version of the IDE.

[UPDATE]  I did a little more digging and it turns out that if I am selecting 10 columns and the 3rd column is of type DATE, all columns from 3 to 10 come back with null values.

Make 100% sure they are all null and don't read "NULL" as a string.  if they are truly null, then it won't error out.

Also the non-null date has to be properly formatted.

I am as clos to 100% sure as I can me -- if I use the native SQLite command line tool to display the data, they show up as null.

Ok, as I continue to research I think I have found the issue, but the behavior is still odd.

The dates are actully formatted like this (valid ISO format, just apparently not valid for sqlite).

MM-DD-YYYY (instead of YYYY-MM-DD)

That said, why is it that not only does the date column come back as null but so does every column after the date (is it possible the code stops processing the results for all remaining columns when an error occurrs)?

Now for the next question, anyone have any suggestions on how to fix the records since I cannot load them into ADO.NET and I cannot seem to find a way to parse them via SQL I am at a bit of a loss.

I am back to thinking this is a bug in the ADO.NET wrapper -- if I use the command line and some of the built in date/time functions everything works as expected.  If this is the case, shouldn't the ADO.NET wrappe work as well?

No, because the command-line doesn't care about strong types, and the ADO.NET provider does.  The ADO.NET provider expects an ISO8601-formatted datetime to be in a datetime field.  If you inserted your datetimes in a funky or region-specific format, then you need to reformat them.

ISO8601 format is a good idea regardless... it's not possible to properly sort a datetime column in SQLite if you have it in MM-DD-YYYY format.

Not sure I agree, but that's ok :)

So the question still stands, any suggestion on how to reformat all of the date strings I have?  I have over 200,000 records, so I cannot exactly drop the table and start over.

It would be a nice feature to have the ADO.NET wrapper use the DateTime.Parse of the DateTime.ParseExact failes (as it would be able to handle this string without issue).

 Ok so only way to avoid this is manually writing a method to reformat any dates to that format.