Writing to datetime2 column in SQL Server with odbc

[chicotobi]

I posted here about writing to a datetime2 column in SQL server with odbc: https://stackoverflow.com/questions/78350006/how-do-i-make-datetime2-work-with-odbc-dbi-in-r/78366002#78366002

con <- odbc::odbc() |> odbc::dbConnect(
  driver = "ODBC Driver 18 for SQL Server",
  uid = "",
  pwd = "",
  database = "",
  server = "",
  encrypt= "no"
)
ts <- lubridate::now("UTC")
options(digits.secs=6)
ts

Output: [1] "2024-04-22 12:38:47.364887 UTC"

Now I have a timestamp with six digits. Add it to a table with a POSIXctcolumn and write it to a table with datetime2(7) column col_datetime2:

df <- data.frame(col_datetime2 = ts)
con |> odbc::dbAppendTable("test", df)

But that doesn't work, value in the database is 2024-04-22 12:38:47.3640000. Somehow during transfer, precision six is reduced to three and written to the database.

But! Now use a table with character column.

df <- data.frame(col_datetime2 = as.character(ts))
con |> odbc::dbAppendTable("test", df)

Value in the database is 2024-04-22 12:38:47.3648870. How/why does that even work? I would expect that odbc complains, because the type (character != datetime2) doesn't match? But it seems, a correct insert command is generated and sent to the database, and it even has the correct precision.

Anyway, I would be happy if someone explains, why the character cast works and if it should be included within odbc? So a user can send a data.frame with a POSIXct column and the data gets handled in the correct way?

[detule]

Hi - thanks for your comment.

For reference - different incarnations of this issue here and here.

If you read through some of the history - you can see that the fixed precision for datetime2 is by choice to avoid needing to query the database for the (user-configurable) precision of the field in the target table. I think we best leave that as is for now, though these days we have a bit more access to the query parameter descriptions and may be able to make some progress without much of a performance penalty.

For what is worth, writing a character column works because at that point, rather than package::odbc doing the data structure conversion ( [R] data type, to C, to SQL ), you are leaving it to the driver to do it for you. In this instance SQL Server look to be smart enough to do this conversion correctly.

I'll close this for now, and open a feature request and jot some thoughts down on a possible approach / tackle when we get a chance.