RSQLite - dbWriteTable - field.type - How to get proper Date format?

Question

RSQLite::dbWriteTable function, properly pass Dates from df to db.

I have some date fields on my df, and when I try to write the table on an SQLite db using the aforementioned function and setting field.types = c(col1 = "Date", col2 = "Date") it writes those date fields as a number instead of date, e.g. :

"2018-12-01" becomes 17866

Here's my complete code:

dbWriteTable(db, "table", df, overwrite = T, append = F, 
          field.types = c(Col1 = "Date", Col2  = "Date"))

This actually writes the "table" on the SQLite db with proper Date format for such columns, but the values inside the cells are not dates, they remain numbers such as 17866.

Here I found someone suggesting a workaround, transforming the date as.character.

Is there a proper way to pass a date value to an SQLite db?

Thank you in advance

Answer 1

Reviewing now, I never actually found a way to do this as described on my question. In the end I went for the workaround that I mentioned in my question:

transform all the date variables in text before writing on the SQLite DB.