dbReadTable coercing date column from SQL database to character

Question

I can write a date to an SQL table with DBI::dbWriteTable() and see that the column is in fact formated as a date on the database. But when I use DBI::dbReadTable() the same date column is coerced to a character column in R.

How do I read the SQL table into R and preserve the date column format?

library(DBI)

df <- data.frame(date = as.Date("2012-01-01"))
class(df$date)
#> [1] "Date"

udt <- dbConnect(odbc::odbc(), "udt")
dbWriteTable(udt, name = Id(schema = "FarmingAnalytics", table = "test"), value = df, overwrite = TRUE)

df2 <- dbReadTable(udt, name = Id(schema = "FarmingAnalytics", table = "test")) 
class(df2$date)
#> [1] "character"

^{Created on 2020-01-20 by the reprex package (v0.3.0)}

Answer 1

This works for me

df2$date <- as.Date(df2$date)

And more generically

datecols <- dbGetQuery(udt, "SELECT  COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE UPPER(TABLE_SCHEMA)='FARMINGANALYTICS' AND 
                UPPER(TABLE_NAME)='TEST' AND DATA_TYPE='date'" )
for (col in datecols$COLUMN_NAME){
  df2[[col]]<- as.Date(df2[[col]])
}