[SOLVED] Writing dates to SQL Server data from R

Writing dates to SQL Server data from R

I am saving a data.frame through odbc to an SQL Server database in a new table. The data.frame contains dates in one column.

I am using the following commands:

require(odbc)
require(DBI)

MyData <- data.frame(Date = as.Date(c("2017-05-17","2017-05-18"), format = "%Y-%m-%d"))

conn <- DBI::dbConnect(drv = odbc::odbc(), dsn='MyDatabaseSource')

dbWriteTable(conn = conn, name = 'dbo.MyTable', value = MyData, overwrite = T)

However, this results in the following error message:

Error in result_insert_dataframe(rs@ptr, values) : nanodbc/nanodbc.cpp:1587: 22007: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string.

I can resolve it by changing the column type to character, but then the column is stored as varchar(255) in the database. How do I store the Date column in the date format in the database and not varchar?

Solution

The problem was solved by using the language-independent date format yyyymmdd as suggested by sepupic in the comments. I did this by changing date column to chr in the yyyymmdd format and then back to the date format:

MyData$Date <- as.Date(format(MyData$Date, "%Y%m%d"), format = "%Y%m%d")