Subsonic 3.0 SQLite FormatException parsing DateTime?

Question

I have an MS Access database, which I have converted to an SQLite database. I have SubSonic setup and working, and I can pull data out of the database successfully into a WinForms app using ActiveRecord. All except for one table.

I get a FormatException, "String was not recognized as a valid DateTime". The format of the date column in the database is DD/MM/YYYY.

I'm not even trying to do anything too complicated:

 var allOrders = order.All();

 foreach (order o in allOrders)
 {
   listBox1.Items.Add(string.Format("{0} - {1}", o.OrderDate.HasValue ? o.OrderDate.Value.ToShortDateString() : string.Empty, o.Product));
 }

I'm not exactly sure why the problem is manifesting in the first place :(

Answer 1

SQLite has "interesting" ways of formatting dates - you have to be very, very sure that you're getting back what you think you are because normally it's not DD/MM/YYYY - it's the opposite if I recall.

Answer 2

I have the same issue. I'm getting dates back in "MM/DD/YYYY HH:MM:SS AM" format with sqlite3.exe, and I get the same exception. The odd thing is I'm getting it very early, using the DataReader class of that library:

var name = reader.GetName(index);

var val = reader.GetValue(index);

the exception throwns on the GetValue call with this stack trace:

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.SQLiteConvert.ToDateTime(IntPtr ptr, Int32 len) 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)

I'm testing using a Northwind database that has been converted to sqlite. I'm not sure how to go forward, this seems pretty fundamental!