Error updating datetime field in SQL server

ankane / dbx

A fast, easy-to-use database library for R

Other

187 stars 15 forks source link

Error updating datetime field in SQL server #27

Closed rochifebo closed 3 years ago

rochifebo commented 3 years ago

Hi,

I'am using dbxUpdate to update a SQL Server 2019 table and I get this error

Error: nanodbc/nanodbc.cpp:1655: 22007: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. 
<SQL> 'UPDATE "Completed_Actions" SET "END_DATE" = '2021-04-01 00:00:00.000000' WHERE "ID" IN (37505674, 37505675)'

the error is related to the fact that SQL Server datetime columns are only able to store fractional seconds to millisecond precision (3 decimal places).

Thank you for the useful library and your support

ankane commented 3 years ago

Hey @rochifebo, thanks for reporting! It's not ideal, but you should be able to covert the column to a string before passing to dbxUpdate:

records$column <- format(records$column, tz="Etc/UTC", "%Y-%m-%d %H:%M:%OS3")

dbx could lookup the column type to differentiate between datetime and datetime2, or have an option for the precision to use for POSIXct values, but I don't think it's worth the extra complexity right now.