Dapper: String was not recognized as a valid DateTime

Question

i am using Dapper for accessing sqlite database and getting 'String was not recognized as a valid DateTime.'. Any help is appreciated.

// Create table schema
CREATE TABLE "mytable" ( "field1"   TEXT, "field2"  TEXT, "field3"  TEXT, 
                         "field4"   TEXT, "field5"  TEXT, 
                         "field6"   bit, 
                         "field7"   TEXT, "field8"  TEXT, "field9"  TEXT,
                         "field10"  TEXT, 
                         "field11"  DateTime )

Query code :

var result= sqliteConnection.Query<TestItem>("Select * from mytable");

Query to insert data:

INSERT INTO "main"."mytable" ("field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10", "field11") VALUES ('750eb223-2993-4d85-9d4f-3e8689e9baa7', 'some value', '', 'some value', 'some value', '1', '84', 'ae35e1e1-dd4c-4e49-a76c-d577f417bf9a', 'some value', 'HOME.aspx', 
                             '2020/01/20 17:38');
INSERT INTO "main"."mytable" ("field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10", "field11") VALUES ('750eb223-2993-4d85-9d4f-3e8689e9baa7', 'some value', 'asdf', 'some value', 'some value', '1', '32', 'a1cd1b8f-95f6-4b03-8d54-f904c21749ac', 'HOME.aspx', 'HOME.aspx', 
                              '2020/01/20 17:38');
INSERT INTO "main"."mytable" ("field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10", "field11") VALUES ('750eb223-2993-4d85-9d4f-3e8689e9baa7', 'some value', 'some value', 'some value', 'some value', '1', '99', 'b9e63bfd-c73e-4e9a-b3e7-30ae49d8a002', 'CALLSS.aspx', 'CALLSS.aspx', 
                              '2020/01/20 17:38');

Error Message:

Error parsing column 10 (field11=HOME.aspx - String)

Inner Exception Message:

String was not recognized as a valid DateTime.

StackTrace:

at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3609
   at Dapper.SqlMapper.<QueryImpl>d__138`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1100
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723
   at Tzunami.LinkResolver.DatabaseMigration.Models.DBMigrator.<MigrateDeploymentListItemAsync>d__5.MoveNext() in C:\Users\surendra\source\repos\Tzunami.LinkResolver.MigrationTool\Tzunami.LinkResolver.DatabaseMigration\Models\DBMigrator.cs:line 77

Mapping item class:

public class TestItem
{
  public string Field1 { get; set; }
  public string Field2 { get; set; }
  public string Field3 { get; set; }
  public string Field4 { get; set; }
  public string Field5 { get; set; }
  public string Field6 { get; set; }
  public string Field7 { get; set; }
  public string Field8 { get; set; }
  public string Field9 { get; set; }
  public string Field10 { get; set; }
  public string Field11 { get; set; }
}

Database schema and entries can be seen from following example taken from DB SQLite browser

Answer 1

Firstly: Keep in mind that as mentioned in Sqlite Documentation there is no Datatypes as DateTime it is stored as a text or some numbers somehow so its better to save dates as plain text during DB cretion. Documentation here: https://www.sqlite.org/datatype3.html

Secondly: It is Expected DateTime to be on ISO8601 format i.e. "YYYY-MM-DD HH:MM:SS.SSS" format but as data on table it is "YYYY-MM-DD HH:MM" it cant be parsed as a DateTime.

Workaround:

As You can't Change DB schema and data, connection.ExecuteReader works quite well on these circumstances, so use connection.ExecuteReader and to get column value as string use reader.GetString() Method.

Answer 2

I never used SQLite but as per document, SQLite does not have datatype to store DateTime.

2.2. Date and Time Datatype
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.

Considering above, your date format in following code:

INSERT INTO "main"."mytable" (..., "field11") VALUES (..., '2020/01/20 17:38');

should be

YYYY-MM-DD HH:MM:SS.SSS

This way, you do not need to change the database schema; just a change in your code.