2

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.

Gang Liang
  • 793
  • 1
  • 9
  • 19
  • This is a known floating-point precision issue. Also, `z` is a `POSIXlt` object, not `POSIXct`. There's no issue if you convert to `POSIXct`. – Joshua Ulrich Aug 21 '13 at 20:53

1 Answers1

2

What's your OS? 

R> z <- strptime("20/2/06 11:16:16.683", "%d/%m/%y %H:%M:%OS") 
R> z
[1] "2006-02-20 11:16:16.683"
R>

No problem here using Ubuntu.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725