sqldf strfmt changes format?

[yoasty]

I have run into a problem. I am transferring a small sqlite database to rstudio-cloud.
The problem may be related to both R and sqlite having a function strfmt.

In sqllite I can run:
SELECT os.date,strftime('%Y%m',os.date) as 'yyyymm', os.os, os.visits and the result will have a column yyyymm with the year and month as a string. Handy for grouping etc.

<- sqldf('select os.date, strftime("%Y%m",os.date) as "yyyymm", os.os, os.visits but the result is a number.

[yoasty]

The problem is almost certainly due to the fact that tables in sqlite do not automatically get their date-formats set correctly.

usin the DBI package to write to a sqlite database works, but format does not appear to be set automatially.

filling the table with
library(DBI)
library(tidyverse)
library(plyr)
library(sqldf)

#-- !preview conn=DBI::dbConnect(RSQLite::SQLite(),"/cloud/project/testsqlitedb.sqlite")
conn <- DBI::dbConnect(RSQLite::SQLite(),"/cloud/project/testsqlitedb.sqlite")
os <- read_csv('os.csv')
dbListTables(conn)
dbWriteTable(conn, "os", os)
dbListTables(conn)
dbDisconnect(conn)

followed by this sql
-- !preview conn=DBI::dbConnect(RSQLite::SQLite(),"/cloud/project/testsqlitedb.sqlite")
select os.date,os,visits from OS

displays the date as numbers

date os visits
|17994| (not set) |17023|
|17994| Android |9373375|
|17994| BlackBerry |5433|