RODBCExt: sqlExecute date problem

Question

I tried to use the parameters in the request, but I (or my system) had a problem with dates.

I am connecting to MS SQL Server

and determine the specific date that I want to process

endDate <- '02.08.2015' My query looks like this:

 test <-"SELECT [RDate],[Currency_ID],[Rate],[NRate] FROM [dwh].[dbo].FC_CurrencyRate] WHERE RDate = ? " sqlExecute(myconn,test, endDate) 

This gives the following error:

Error in sqlExecute (myconn, test, endDate): 22018 0 [Microsoft] [SQL Server Native Client 11.0] Invalid character value for cast specification

[RODBCext] Error: SQLExecute failed

Optional: Warning message: In sqlExecute (myconn, test, endDate): 22018 0 [Microsoft] [SQL Server Native Client 11.0] Invalid character value for cast specification

Then I tried changing the date format

 endDate <- as.Date(endDate, format='%d.%m.%Y') sqlExecute(myconn,test, endDate) 

but this leads to another error

Error in sqlExecute (myconn, test, endDate): 07006 0 [Microsoft] [SQL Server Native Client 11.0] Attribute violation with limited data type

Error [RODBCext]: SQLBindParameter Error

Optional: Warning message: In sqlExecute (myconn, test, endDate): 07006 0 [Microsoft] [SQL Server Native Client 11.0] Attribute violation with limited data type

Is this a typical problem? Are there other types of date format using R?

Did I also try covering a question mark? quotes like ??. This caused Rstudio to crash and the request time was too long.

Any hint is appreciated

Answer 1

I just took the time to figure out how to pass the date to my DB2 database as a parameter using sqlExecute in R. I could not find a short answer anywhere, and I got this type of output:

 > sql [1] "select date from date where date = ?" > sqlExecute(conn, sql, data = as.Date("2015-01-01")) Error in sqlExecute(conn, sql, data = as.Date("2015-01-01")) : 07006 -99999 [IBM][CLI Driver] CLI0102E Invalid conversion. SQLSTATE=07006 [RODBCext] Error: SQLBindParameter failed 

Reading through the RODBCext documentation I came across this line from Zozlak Source

• date dates using as.character (as.Date (column))

So I gave him a move:

 > sqlExecute(conn, sql, data = as.character(as.Date("2015-01-01")), fetch = T) DATE 1 2015-01-01 

OMG! This is WORKS !!!

Hope this helps someone. I can’t say if this is a consistent solution for different DBMSs, but it works fine for my connection to DB2.

Answer 2

Decision

The date must be transmitted in the format accepted by the ODBC database driver. By definition, YYYY-MM-DD should always be supported .
Other date formats may be supported depending on your ODBC driver.

As a default, R prints Date objects according to ISO 8601, which is also YYYY-MM-DD
as.character(as.Date(yourDate, format='see_as.Date_documentation')) should do the job.

Why as.Date(myDate, format='myFormat') not work?

• R does not have a separate internal data type for dates. Deep in their souls. R-dates are numerical vectors storing the number of days from 1970-01-01 with the class attribute set to "Date" and the print.Date() function that displays them as dates instead of numbers.
• The RODBCext package only processes selected internal R data types: integer, double (numeric) and character (and all other internal R data types are transferred to a character).

Now, since R-dates are internal numbers (a numeric number), what is passed to the ODBC driver can be seen using as.numeric(as.Date(yourDate)) , which is clearly not like the date for the ODBC driver.

Of course, you can say that dates are used so often that RODBCext must take care of R Date objects. In this case, fill out https://github.com/zozlak/RODBCext/issues , requesting such a function.

Answer 3

I have no experience with the R language, but I think that it cannot convert the date type to the SQL datetime type. You can try converting it to datetime using the as.POSIXct function.