Writing dates to SQL Server data from R

1cosmwyk 于 2023-10-15 发布在 SQL Server

关注(0)|答案(1)|浏览(90)

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 ?

sql-server

来源：https://stackoverflow.com/questions/47752362/writing-dates-to-sql-server-data-from-r

1条答案

按热度按时间

ncecgwcz1#

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")

赞(0）回复(0）举报 2023-10-15

我来回答

Writing dates to SQL Server data from R

1条答案

相关问题

热门标签

最新问答