SQLite ExecuteReader --> DataTable.Load --> FormatException (DateTime)

Question

I tried to read out a sample Northwind sqlite database but get an error on some tables with a datetime. Is this a problem of the database or of my System.Data.SQLite? The Exception is somethin like: "the string is not a valid DateTime"

http://system.data.sqlite.org/

Of cource I can read out the data by myself with the correct converting of datetime but this is not as performant as reading it out via a simple dt.Load()

        SQLiteCommand dbCommand = myConnector.CreateCommand();
        dbCommand.CommandText = "SELECT * FROM " + tablename;

        SQLiteDataReader executeReader = dbCommand.ExecuteReader(CommandBehavior.SingleResult);
        DataTable dt = new DataTable();
        dt.Load(executeReader); // <-- FormatException

" bei System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)\r\n bei System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)\r\n bei System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConvert.cs:Zeile 322."

I need a good help to improve the current code.

Answer 1

Yep, I faced the same problem, and I've found a solution.

First of all you need to know why it happens.

http://www.sqlite.org/datatype3.html

**

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

**

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. 

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

For more date/time functions you can see this page: http://www.sqlite.org/lang_datefunc.html

SOLUTION:

Is just use a datetime function to convert your field into a valid .NET datatable format. You can use anyone from the above link, in my case I choose:

SELECT datetime(field) as field FROM table

TIP 1 If your query is on a view, and you can't touch your db, create a different view or something like, you are in a little trouble because the problem is on the db side as I explain before. So the first solution is replace the column of a datatable before load (a little dirty work). Or you can use a Unix stamp on a text field, or any desired timestamp. But I think the best solution is create your query on your application from your view:

SELECT datetime(field) as field FROM view

TIP 2

You can try also with the SQLite Administrator and play with datetime functions before to check the format.

TIP 3

Be aware of your culture globalization also, you can find a problem if you change the culture. Try to your soft work with only one and display it as you want, not as the culture wants. In my country for eg, we use as common this format: dd/MM/yy and is a pain, like the dot on decimal numbers (we use a comma)

TIP 4

If you are working with a wizard-defined connection on some report or entity or something like, be sure to check your desired conversion format. For example I use the most common and non problematic way: ISO8601

Answer 2

Did you try with SQLiteDataAdapter like so..

SQLiteDataAdapter da = new SQLiteDataAdapter(dbCommand);
da.Fill(dt);

Chances are it behaves the same but it doesn't hurt to try ;)

Answer 3

See this publication about SQLite and DateTime storage, under the heading of Storing DateTimes.