Sqlite Datetime Handling In Sqlprovider

How to use DateTime with SQLite via the SQLProvider type-provider? SQLite doesn't really have a date and time datatype (see Data types) and stores dates as text. I can pass a date

Solution 1:

DateTime type is working as intended with the following versions with SQLProvider 1.0.31 and SQLite 1.0.102:

#if INTERACTIVE
#I @"..\packages\SQLProvider.1.0.31\lib"
#r "FSharp.Data.SqlProvider.dll" 
#I @"..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"
#r "System.Data.SQLite.dll"
#I @"..\packages\System.Data.SQLite.Linq.1.0.102.0\lib\net46"
#r "System.Data.SQLite.Linq.dll"
#endif

open System
open FSharp.Data.Sql
//open System.Data.SQLite
//open System.Data.SQLite.Linq

[<Literal>]
let connectionString = "Data Source="+ @"C:\tmp\databaseFile.db3"
[<Literal>]
let resolutionPath = __SOURCE_DIRECTORY__ + @"..\..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"

type sql = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE, 
                connectionString, 
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

let ctx = sql.GetDataContext() 

let table2 = ctx.Main.Table2   //DateTime
let table3 = ctx.Main.Table3   //Text