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

Question

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,

Answer 1

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)