Rodbc Loses Time Values of Datetime When Result Set Is Large

SQL-query loses information

Solution #1

You can use the answer from kristang: (call sqlQuery with as.is option, get timestamps in a string and convert the column with as.POSIXct in R).

Solution #2

But I believe more efficient solution is to get the datetime values in numeric type via SQL expression (example for SQL server):

sqlQuery( "select convert(float, my_date)*3600*24 as my_date from ...")

and convert it to POSIXct from number:

df1$my_date <- as.POSIXct(df1$my_date, origin = "1900-01-01", tz = "UTC")

Since POSIXct is numeric at heart, fetching and type converting runs faster than normal sqlQuery with RODBC. RODBC converts every timestamp from text string (look as.POSIXct usage in the sqlGetResults function). So the solution is reasonable even with RODBC returning complete timestamps.

P.S. If you really like converting from text see fastPOSIXct from fasttime package.

Maximum Length of Value in R Data Frame, RODBC

I did an end to end test on DB2 (LUW 9.7) and R (3.2.2 Windows) and it worked fine for me.

SQL code:

create table test (foo varchar(3000));

--actual insert is 3000 chars
insert into test values ('aaaaaa .... a'); 

--this select worked fine in my normal SQL client
select * from test

R code:

long = sqlQuery(connection, "select * from test");

#Displays the 3000 character value.
long;

My guess is the problem is for some other reason than simply the size of the field:

• Character encoding issues. If you are seeing something funny in Access, perhaps the content of the field is something not acceptable in the character encoding R is using, so it is being discarded. (I'm not familiar with character encoding in R in particular, but it is in general a thorny issue for software development).
• Overall size of the results. Maybe the problem is due to the overall length of a row rather than the length of a single field. Is the query also returning lots of other stuff? Have you tried a simple test of just this field?
• Problem in another version. Maybe you are using a different version than I was, and there is indeed a problem with your version. If you think so, update your question with more information.

RODBC returning 0 rows even though there are many rows

The first thing you can do is to change the working date incrementally until you discover a pattern that causes the query to fail. Perhaps (for future readers) the problem is that SQL Server is expecting a format other than the standard. If that's the case then a query for the first day of the first month would (by coincidence only) return the proper result. You can use set dateformat to get SQL Server to accept the format you need.

A way to extract from a DateTime value data without seconds

For a solution that truncates using strings try this:

SELECT CAST(CONVERT(CHAR(16), GetDate(),20) AS datetime)

---

CHAR(16) works only if our variable is converted to ODBC canonical format, as shown above by using 20 as the format specifier.

DECLARE @date DateTime = '2011 Nov 22 12:14:55';
SELECT CONVERT(Char(16), @date ,20) AS datetime

Results:

| datetime         |
|------------------|
| 2011-11-22 12:14 |

Then you simply cast back to a DateTime type to continue using the value.

NOTE: This is only viable for data types that do not carry TimeZone info.
Also type conversions to VarChar and back are usually LESS performant than using DateTime functions that use numeric operations internally.

Consider other solutions posted if performance is a concern or if you must retain timezone information.

---

Related Topics