How RODBC add the dynamic date for DB2, need a single quote

Question

I add a dynamic date '", dt, "'for my code as follow.

I first put the date as string, and then paste into sqlQuery.

today<-Sys.Date() 
monthStart <- function(x) { 
x <- as.POSIXlt(x)
x$mday <- 1
as.Date(x)
}
dt_date<-monthStart(today)-1 
dt<-as.character(dt_date) 

df<-sqlQuery(db2,paste("
Select SUM_DATE from database where SUM_DATE= '", dt, "'
"), as.is=TRUE)`

but the error says:

[1] "22007 -180 [IBM][CLI Driver][DB2] SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007\r\n"   

I am sure the date format should be '2018-02-28' so if I replace '", dt, "' with '2018-02-28', the code above works fine.

I guess it is because R string is double quote but db2 wants a single quote. So how can I solve this problem?

Any idea is appreciated and thank you!

Answer 1

Since the default in paste uses sep=" ", your query reads with such spaces in the date value:

Select SUM_DATE from database where SUM_DATE= ' 2018-02-28 ';

To resolve consider either paste0 or paste with sep=""

sql <- paste0("Select SUM_DATE from database where SUM_DATE= '", dt, "'")
df <- sqlQuery(db2, sql, as.is=TRUE)

Even better is to use parameterization, the industry best practices in running SQL in application layer like R and you can do so with extension package, RODBCext.

library(RODBCext)

df <- sqlExecute(db2, "Select SUM_DATE from database where SUM_DATE = ?", dt, fetch=TRUE)