Search code examples
sql-serverrrodbc

RODBCExt: sqlExecute issue with dates

I was trying to use parameters in a query but I (or my system) seems to have problem with dates.

I'm connection to a MS SQL Server

and define a specific date I want to handle

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

In addition: 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 to change the dateformat

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

but this leads another error

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

[RODBCext] Error: SQLBindParameter failed

In addition: Warning message: In sqlExecute(myconn, test, endDate) : 07006 0 [Microsoft][SQL Server Native Client 11.0]Restricted data type attribute violation

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

What i also tried was embracing the question mark ? by quotes as '?'. This lead to a crash of Rstudio and the query time was way too long.

Any hint is appreciated

Solution

  • I have just spent ages trying to work out how to pass a date through to my DB2 database as parameter using sqlExecute in R. I couldn't find a concise answer anywhere and I was getting 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 stumbled across this line from Zozlak Source

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

    So I gave it a go:

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

    OMG! It WORKS!!!

    I hope this helps someone. I can't tell whether this is a consistent solution for different DBMS but for my DB2 connection it works just fine.