This question already has an answer here:
- Odd behavor with POSIXct/POSIXlt and subsecond accuracy 1 answer
- R xts: .001 millisecond in index 2 answers
When working to update a sql server table with a datetime field, I had the following observations:
> options(digits.secs = 3)
> z <- strptime("20/2/06 11:16:16.68", "%d/%m/%y %H:%M:%OS")
> z
[1] "2006-02-20 11:16:16.67"
> z <- strptime("20/2/06 11:16:16.683", "%d/%m/%y %H:%M:%OS")
> z
[1] "2006-02-20 11:16:16.682"
We can see that there is a loss of precision on the POSIXct side.
Anyone has similar experience? I think it is a bug if can be confirmed... Thanks!
EDIT
Thanks a lot as many people pointed out that similar problems were asked before. It actually came up from an RODBC issue... and glad that I found a solution.
require(RODBC)
ch <- odbcConnect('mydb')
/* mybus (Dt datetime, value float) */
sqlstmt <- 'select * from mybus;'
mybus <- sqlQuery(ch, sqlstmt)
mybus$value <- mybus$value + 1
sqlUpdate(ch, mybus, index='Dt', verbose=T)
close(ch)
**[RODBC] Failed exec in Update**
My digging shows that the error is due to the mybus$Dt read from sql server (after R processing) is slightly different from the original ones stored in sql server; hence, the sqlUpdate operation failed -- since they can't find match. I also tried datetime2 but to not avail.
My current workaround is to convert datetime to characters at the sql server side:
sqlstmt <- 'select convert(nvarchar(24), Dt, 21) as Dt, value from mybus;'
to bypass the R POSIXct transformation.
