r向SQL查询输入的闪亮日期范围 [英] r shiny date range input to sql query

问题描述

我下面有一个可以正常工作的sql查询.

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 

我正在修改此查询，以使其采用闪亮的UI(日期范围)中的日期输入.

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"))

我收到错误

[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 '"

不确定如何解决此查询，以便它需要从闪亮的UI中输入日期，需要帮助.

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

推荐答案

我使用sub函数通过简单地使用正则表达式来做到这一点:

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)

这篇关于r向SQL查询输入的闪亮日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！