sqlroraclerodbcoracle-rdb

R RDB Query of date using RODBC Connection

I am trying to query an old Oracle RDB database using something like Sys.Date()-1 as that works in R, but I have not been able to find the right syntax.

The following works but I want to run it on a regular schedule so a fixed time frame will not work :

`Output <- SQLQuery ("SELECT * FROM tablename WHERE productionDate BETWEEN 20-FEB-2017 00:00:00 AND 21-FEB-2017 23:59:00")`

I would like to have something like:

Output <- SQLQuery ("SELECT * FROM tablename WHERE productionDate >= Today()-1")

I have also tried assigning macro variables outside of the query and then call the. Inside the query with no success. The entries go back several years so to take everything takes a couple minutes to run the query and then I have to subset the data after the fact. I would hope there was a better way for R to query a database even an old one based on dates.

Thank you for any help.

Solution

  • something like this?

    startDt <- as.Date("2016-02-12")
endDate <- as.Date("2016-03-12")
sqlstr <- paste0("SELECT * FROM tablename WHERE productionDate BETWEEN '",
    paste(format(c(startDt, endDate), "%d %B %Y"), collapse="' and '"),"'")
sqlstr