How to write sql query with variable in R?

Question

I'm using dbGetQuery in R to fetch result.

df <- dbGetQuery(conn, "SELECT * FROM orders WHERE invoice_date >= '2020-08-31 00:00:00.00000+00'")

I want to assign date to a variable and call it in query. Something like this,

invoice_date <- '2020-08-31 00:00:00.00000+00'
df <- dbGetQuery(conn, "SELECT * FROM orders WHERE invoice_date >= {invoice_date}")

How can I achieve this?

I tried this code paste("SELECT invoice_date,order_id, product_code, delivery_pincode,fulfilment_center FROM sales_report WHERE invoice_date >= ", invoice_date, "", sep="") but it is not taking as character with quotes — hidden-layer
– hidden-layer, Commented Sep 4, 2020 at 14:20
After @Waldi's response I tried this with paste option, it works. invoice_date <- "'2020-09-03 00:00:00.00000+00'" df <- dbGetQuery(conn, paste("SELECT invoice_date,order_id, product_code, delivery_pincode,fulfilment_center FROM sales_report WHERE invoice_date >= ", invoice_date, "", sep="") .Thanks !! But I'm accepting @Waldi's answer since it's more cleaner approach. — hidden-layer
– hidden-layer, Commented Sep 4, 2020 at 14:31

Waldi · Accepted Answer · 2020-09-04 14:24:30Z

1

The glue package should perfectly fit your needs:

invoice_date <- '2020-08-31 00:00:00.00000+00'
df <- dbGetQuery(conn, glue::glue("SELECT * FROM orders WHERE invoice_date >= '{invoice_date}'"))

Another option is to include the quotes in the variable:

invoice_date <- "'2020-08-31 00:00:00.00000+00'"
df <- dbGetQuery(conn, glue::glue("SELECT * FROM orders WHERE invoice_date >= {invoice_date}"))

edited Sep 4, 2020 at 14:24

answered Sep 4, 2020 at 14:14

Waldi

41.6k6 gold badges38 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hidden-layer Over a year ago

The assigned variable is not taking as character with quotes. I got this error WHERE invoice_date >= 2020-09-04 00:00:00.00000+00 (ERROR: syntax error at or near "00" Position: 137) . Quotes are missing .

Amit Over a year ago

what if we have multiple where condition like --> where Material= "car" and invoice_date >= {invoice_date}

Waldi Over a year ago

@Amit, you could build the where condition as needed, possibly with if statements, for example

mycondition <- "where "; mycondition <- paste(myconditon, " Material= 'car'"; mycondition <- paste(mycondition, glue("and invoice_date >= {invoice_date}")

Collectives™ on Stack Overflow

How to write sql query with variable in R?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related