How do you write a table to DB using DBI/dbplyr and preserve date field?

Question

When I use {DBI} and {dbplyr} to write a table containing a date-type field to a database, the field is forced to a character. How do I make it write to the SQL Server database table as a date type?

library(DBI)
library(odbc)
library(dbplyr)

con.write <- dbConnect(drv = odbc(),
                       Driver = "SQL Server",
                       Server = "MYSERVER",
                       Database = "MYDATABASE")

cars <- mtcars %>%
  mutate(AsOfDate = as.Date('2022-01-24'))

Check to see the class of the date field:

> class(cars$AsOfDate)
[1] "Date"

Now write to the database and re-read from the database.

dbWriteTable(con.write, name = "tmp_mtcars",
             value = cars, row.names = TRUE)

dbcars <- tbl(con.write, "tmp_mtcars") %>% collect()

Observe the class of the field we just wrote to the database:

> class(dbcars$AsOfDate)
[1] "character"

Answer 1

One workaround is to use as.POSIXct instead of as.Date:

cars <- mtcars %>%
  mutate(AsOfDate = as.POSIXct('2022-01-24'))

dbWriteTable(con.write, name = "tmp_mtcars",
             value = cars, row.names = TRUE)

dbcars <- tbl(con.write, "tmp_mtcars") %>% collect()

> class(dbcars$AsOfDate)
[1] "POSIXct" "POSIXt" 

I'm not sure what the functional effects are for using as.Date vs as.POSIXct.