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,

score 32 · Answer 1 · answered Oct 23 '14 at 21:13

32

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)

answered Oct 23 '14 at 21:13

erichoxworth

321
3
2

I think there are a bug in C# SQlite wrapper, the default datetimeformat is ISO8601, but forcing to CurrentCulture work great. – themadmax Feb 20 '15 at 11:02
2

Which version of system.data.sqlite are you using, i tried th CurrentCulture, e but i get an error `Requested value 'CurrentCulture' was not found.` – Smith May 05 '15 at 15:11
1

`CurrentCulture` didn't work for me. `Ticks` solved the problem. – Draex_ Aug 17 '17 at 12:55
@erichoxworth With your additional parameter, I get this issue mostly resolved. The only problem are some strange contents like "\N" instead of real date. I found no other solution then TRY...CATCH around it. – PeterCo Mar 17 '18 at 19:01

score 10 · Answer 2 · edited Nov 14 '12 at 16:44

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!

Do SQLite addressed this issue? I can't change DB's DateTime format so the problem still resides. When a user changes the date time format on Windows OS my app crashes. :( my SQLite version is 3.15.1.0 and im developing a Windows 8.1 store app.) Thank you. — SurenSaluka, Jul 03 '17 at 11:04

score 2 · Answer 3 · edited Sep 13 '13 at 06:38

2

select strftime('%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

edited Sep 13 '13 at 06:38

Baby Groot

4,637
39
52
71

answered Sep 13 '13 at 06:21

Rahul Jain

21
1

Mahdi Farrokhzadeh · Answer 4 · 2020-01-27T11:55:03.387

1

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"

edited Jan 27 '20 at 11:55

answered Jan 27 '20 at 11:46

Mahdi Farrokhzadeh

11
3

score -2 · Answer 5 · answered Aug 25 '15 at 07:55

-2

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....

answered Aug 25 '15 at 07:55

Bhavesh Patel

79
1
4

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

5 Answers5

Linked