In sqllite Min of date return integer type - for other dbs return date type

[gowthamrao]

Reproducible code

library(Eunomia)
connectionDetails <- Eunomia::getEunomiaConnectionDetails()
connection <- DatabaseConnector::connect(connectionDetails = connectionDetails)
sql <- "select min(DATEFROMPARTS(2021, 1, 1));"
DatabaseConnector::renderTranslateQuerySql(connection = connection, sql = sql)

[schuemie]

Hmmm, DatabaseConnector should have handled that correctly. There's an easy fix I can make to DatabaseConnector, but we could also decide to change Eunomia to use SQLite's extended types, so it supports dates without DatabaseConnector workarounds.

[gowthamrao]

Using extended types - is that a change in Eunomia package. If yes, then i think we should do the change in Eunomia package (which would be simpler)

Other than testing - i dont think sqllite is used for real CDM (?) i.e. are we supporting sqlite for any reason other than testing?

[schuemie]

Eunomia is used for testing and for examples (e.g. exercises in the Book of OHDSI)

[schuemie]

OHDSI/Eunomia#12

[schuemie]

This issue has been fixed, but does require the upcoming version of SqlRender (1.8.0) as well. Note that the variable name must end with 'date' for DatabaseConnector to convert the field to Date:

library(Eunomia)
connectionDetails <- Eunomia::getEunomiaConnectionDetails()
connection <- DatabaseConnector::connect(connectionDetails = connectionDetails)
sql <- "select min(DATEFROMPARTS(2021, 1, 1)) AS my_date;"
DatabaseConnector::renderTranslateQuerySql(connection = connection, sql = sql)
#     MY_DATE
# 1 2021-01-01