1

How to change parameters in R when I extract data from SQL?

Here is my example, I want control the time.

library(RODBC)
library(dplyr)
r <- "

SELECT 
    *
FROM table
where time > '01 May 2015'
"

Con <- odbcConnect(dsn="SQL")
t1 <- Sys.time() 
spynet <- sqlQuery(Con , r)
odbcClose(Con)
zx8754
  • 52,746
  • 12
  • 114
  • 209
Ariel
  • 19
  • 1
  • 6
  • 2
    I'm not sure I understand what you asking, but you can set `time` separately and then use `paste`, for example `time <- "'10 May 2015'" ; r <- paste("SELECT * FROM table where time >", time)`, though I'm not sure `'01 May 2015'` is a valid date SQL wise. – David Arenburg May 10 '15 at 09:00
  • Not working, Thanks. – Ariel May 10 '15 at 09:05
  • 2
    How are your dates defined in your the `time` column in your data base? Maybe `time <- "'2015-05-01'" ; r <- paste("SELECT * FROM table where time >", time)`? – David Arenburg May 10 '15 at 09:06
  • 1
    What's the type of that column? Is it even of type `Date`? Either way, it seems like you have a SQL question rather an R one. – David Arenburg May 10 '15 at 09:11
  • Type date. its R question! Lets say i read the date with Sys.time() and i want to run SQL script form R and put the date in the script. – Ariel May 10 '15 at 09:29
  • 1
    I already showed you how to do this. For example `r <- paste0("SELECT * FROM table where time > '", Sys.Date(), "'")`. But it's mainly depends on the column in your data base. – David Arenburg May 10 '15 at 09:35
  • Could you add to your post output of `select * from table limit 10`? Also, see related post regarding external variables: http://stackoverflow.com/questions/6863817/source-ing-an-r-script-within-a-function-and-passing-a-variable-through-rodbc – zx8754 May 10 '15 at 10:08

1 Answers1

0

Try below:

#format the date as string to match database format
t1 <- format(Sys.time(), format="%B %d %Y")

r <- paste0("SELECT * FROM table WHERE time > '", t1, "'")

#run the query
sqlQuery(Con , r)
zx8754
  • 52,746
  • 12
  • 114
  • 209
  • While copying my line you forgot to switch `Sys.Date()` to `t1` :) Also you could use `Sys.Date()` in `t1` instead of `Sys.time()` – David Arenburg May 10 '15 at 10:14
  • @Ariel quick Google search tells us that error occurs [when the table doesn't have the column that we are querying for](https://www.drupal.org/node/2007388). Also, `TIME` is a SQL [reserved word](https://www.drupal.org/node/141051). – zx8754 May 10 '15 at 18:28