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 popul

Solution 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)

Solution 2:

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!

Solution 3:

selectstrftime('%m/%d/%Y',substr(colName,0,20)) from tablename

This will work, I have tested this, because built in functions in sqlite converts string to datetime format

Solution 4:

Changing the string for connection didn't work for me but the trigger idea help me use it this way

"Select Cast("Coulname" as nvarchar(20)) From "Table Name"

Solution 5:

Error "String was not recognized as a valid DateTime."

Change your system date time format to you database fild datetime format.

i hope it help you....