[SOLVED] How do I make datetime2 work with odbc, DBI in R

How do I make datetime2 work with odbc, DBI in R

I connect to a SQL Server, where I have a table with a column col_time of datatype datetime2(7) supporting seven decimals (100 ns precision).

con <- odbc::odbc() |> DBI::dbConnect(...)

I have a time:

ts <- lubridate::now()
tbl <- tibble::tibble(col_time = ts)

I want to write that now to my database:

con |> dbWriteTable("test", tbl, append = T)

But the time is only saved to the table with three decimals. How come? If I write SQL code directly in SQL Server Management Studio, of course, it works e.g.

INSERT INTO TABLE test (col_time) 
VALUES ('2024-01-01T12:34:56.1234567')

What's going on? I have a feeling that odbc doesn't support datetime2? Do I have to change something? Would that help? I am using the driver "ODBC Driver 17 for SQL Server".

Solution

A solution using the Native Client 11.0 driver that can be downloaded from Microsoft here. It may work with other drivers but I only had this one.

First connect to the server

library(DBI)

con <- dbConnect(

  odbc::odbc(),

  driver = "SQL Server Native Client 11.0",
  server = "...",
  database = "...",
  Trusted_Connection = "Yes"
)

Configure R to use 6 digits precision which I think is the most R can do

options(digits.secs = 6)

Since passing the time variable directly results in only 3 decimals we convert ts to a string which will contain all the digits

ts <- lubridate::now()
ts <- toString(ts)

Now proceed as in the question code

tbl <- tibble::tibble(col_time = ts)
con |> dbWriteTable("test", tbl, append = T)

If the datatype of the column col_time in the test table on SQL Server is datetime2(7), the variable ts will now have been stored in the test table with 6 decimals.

SQL Server 2008 was used.