r shiny date range input to sql query

Question

I have a sql query below that works just fine.

 Select * from dbo.Employee where StartDate between '08/01/2014' and '08/31/2014' order by StartDate

I am modifying this query such that it takes date input from shiny UI (daterange) .

sqlQuery(myconn, paste("Select * from dbo.Employee where StartTime between", "'as.character(input$daterange[1])'", "and", "'as.character(input$daterange[2])'", "order by StartTime"))

I get an error

[1,] "22007 241 [Microsoft][SQL Server Native Client 10.0][SQL Server]Conversion failed when converting date and/or time from character string."
[,2] "[RODBC] ERROR: Could not SQLExecDirect ' Select * from.....                                                 Where StartDate between 'as.character(input$daterange[1])' and 'as.character(input$daterange[2])' order by StartDate '"

Not sure how to fix this query such that it takes date input from shiny UI, need help.

Whenever I have problems like this, I try to do things piecewise (like first execute the paste statement and make sure that is correct). As I'm seeing it, your paste statement is incorrect, you have quotes around the as.character bit, so you're just pasting that in directly, not the actual value. — goodtimeslim
– goodtimeslim, Commented Mar 9, 2015 at 7:03

Pork Chop · Accepted Answer · 2015-03-09 16:30:43Z

I use sub function to do that, by simply using regular expressions like so:

my_date1 <- "08/01/2014"
my_date2 <- "08/31/2014"

my_query <- 'Select * from dbo.Employee where StartDate between DATE1 and DATE2 order by StartDate'
my_query <- sub("DATE1",my_date1,my_query);my_query <- sub("DATE2",my_date2,my_query)
# the result of your query is below
noquote(my_query)

# if you want the quotes for dates leave them there
my_query <- 'Select * from dbo.Employee where StartDate between "DATE1" and "DATE2" order by StartDate'
my_query <- sub("DATE1",my_date1,my_query);my_query <- sub("DATE2",my_date2,my_query)
# the result of your query is below
noquote(my_query)


# Now sub the inputs into those variables like so
my_query <- 'Select * from dbo.Employee where StartDate between DATE1 and DATE2 order by StartDate'
my_query <- sub("DATE1",input$daterange[1],my_query);my_query <- sub("DATE2",input$daterange[2],my_query)
noquote(my_query)

Collectives™ on Stack Overflow

r shiny date range input to sql query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related