Q:

sqlite throwing a "String not recognized as a valid datetime"

I am playing around with Sqlite and keep getting an error when trying to read back some test data. For example, I created a simple db with a single table and some columns and populated it with some test data as shown below.
sqlite> .schema
CREATE TABLE "shows"(id integer primary key asc autoincrement, showName TEXT, guest TEXT, dateAired date, dateWatched date);

sqlite> select * from shows;
6|test show|test guest 1|2012.05.01|2012.07.10
7|test show|test guest 2|2012.05.02|2012.07.10
8|test show|test guest 4|2012.05.04|2012.07.10

I am using the System.Data.Sqlite library available here , but it keeps giving me an error while trying to read the date column. I tried putting the dates in the dd-MM-yyyy format, but still get an error saying "String Not Recognized as a valid datetime." I have tried using DateTime.Parse or casting it to datetime or just ToString()'ing it to see what happens, but I keep getting the same error. I can read the text fields fine, but can't read the date fields. 
My C# code snipped is given below
var sqliteConn = new SQLiteConnection("Data Source=data/shows.db;Version=3;New=False;Compress=True");
sqliteConn.Open();
SQLiteCommand cmd = new SQLiteCommand(sqliteConn);
cmd.CommandText = "select * from shows";

SQLiteDataReader reader = cmd.ExecuteReader( );
while (reader.Read( ))
    {
    var showName = reader["showName"];
    // This is where it keeps giving me an error.
    // I have tried various things such as DateTime.Parse
    var airedDate = DateTime.ParseExact("yyyy.MM.dd", reader["dateAired"].ToString(), new CultureInfo("en-AU"));
     }
reader.Close( );

Any help would be greatly appreciated.
Regards,

A:

Based on the discussion in this thread, I decided to construct my connection string with the "datetimeformat" parameter and the "String Not Recognized as a valid datetime" issue was resolved.
My example connection string: 
source=<source to db file>;version=3;new=False;datetimeformat=CurrentCulture

This feature was released in version 1.0.87.0 on July 8, 2013 (see release notes)

A:

This thread might be a bit old but I came across the same problem and found a "solution".
It seems that System.Data.SQLite does not handle DateTimes correctly. I tried DateTime.ParseExact and gave the format in my database (mm/dd/yyyy) but I noticed that it threw an error when just calling ToString() on the DataReader column for the date. This seemed strange since I was not attempting to fit it into a Date but have it read out as a String. 
Because of this, I went back to the view I was calling from the database and wrapped all of my dates in a CAST as nvarchar(10). This way, the database will return strings and not dates. Now, the DateTime.Parse works just fine!
I'll use a trigger on the view to pull the string/date back in and insert/update the underlying tables, thus having all the conversions happen in the database, instead of System.Data.SQLite.
There is a new version of System.Data.SQLite coming sometime in December of 2012, I hope this is addressed!

