While I'm reading a complete data table with the Fill() method of SQLiteDataAdapter I get a “String not recognized as a valid datetime” exception.
The exception is thrown while reading a column of type DATETIME NOT NULL which contains Unix epoch time stamps in milliseconds. And yes I know that there is no specific DateTime type in SQLite.
I know (from sqlite throwing a "String not recognized as a valid datetime") that I can adapt the ConnectionString and set "DateTimeFormat=UnixEpoch".
But this setting interprets the value as seconds not milliseconds since January 1, 1970.
Using it anyway results in a ArgumentOutOfRangeException.
There is another parameter for the connection string: DateTimeFormatString. But I don't see a way to use it with my format.
I cannot change the data in the table. And I also don't want to enumerate the columns manually.
Is there a simple way to get the table filled with "select * from ..."? Also just reading it as string would be helpful.
There is a workouround with "DateTimeFormat=Ticks" but this is an ugly solution because it is not based on January 1, 1970.
Any help would be greatly appreciated.
Regards
I stripped down my code to a minimalistic example to demonstrate the behavior.
using System.Data;
using System.Data.SQLite;
namespace SQLite
{
public class Program
{
public static void Main()
{
//using (var connection = new SQLiteConnection(@"Data Source=d:\src\C#\SQLite\SQLite\db\sampledb.db; DateTimeFormat=Ticks;"))
using (var connection = new SQLiteConnection(@"Data Source=d:\src\C#\SQLite\SQLite\db\sampledb.db; DateTimeFormat=UnixEpoch;"))
{
connection.Open();
using (var sqlDataAdapter = new SQLiteDataAdapter("SELECT * FROM TimeStamps;", connection))
{
var dataTable = new DataTable();
sqlDataAdapter.Fill(dataTable);
}
}
}
}
}
Also the the DB now just contains one table. Both dates i.e. 1586948400000 are in Year 2020.
The code above produces an ArgumentOutOfRangeException.
If the code is executed with DateTimeFormat=Ticks in the ConnectionString it runs but creates the wrong calendar dates.
The table was created with this SQL:
CREATE TABLE "TimeStamps" (
"Id" INTEGER,
"TimeStamps" DATETIME,
PRIMARY KEY("Id")
)
