2

I am trying to automate a code where I am pulling data for the last week and run it once in a week. How can I get the Sys.Date() inside an sql query in R.

library('RJDBC')

Tdata<- dbGetQuery(conn, "select roll_id,source,create_date,login from master_data where create_date>'2017-07-17'")

So basically i want to create condition create_date> Sys.Date()-7. How to do it?

Community
  • 1
  • 1
Lesnar
  • 501
  • 3
  • 16

2 Answers2

0

I think your best bet would be to get the current date from SQL query (not R). I believe you can do this with GETDATE().

Another method would be to use the paste0() function in R to construct your query text before passing it into dbGetQuery().

It would be something like query_txt <- paste0("select roll_id, source, create_date, login from master_data where create_date > '", Sys.Date()-7, "')".

Then Tdata <- dbGetQuery(conn, query_txt).

Hope that helps!

Elliot Henry
  • 65
  • 6
0

Try using paste (or paste0):

Tdata <- dbGetQuery(conn,paste0("select roll_id,source,create_date,login 
                    from master_data where create_date>'", Sys.Date()-7, "'"))
Ape
  • 1,159
  • 6
  • 11