CODEWITHSUNDEEP
sqlrdatabase
ltrd
ltrd

Reputation: 326

RODBC - import a table

I would like to import a table but with a dynamic date that I have in vector "Daty". My problem is that I can not import a table with date as a variable.

                select
                    Symbol
                ,   OpenTime
                from
                    xxx t
                inner join
                    zzz i
                on
                    t.xxxxx = i.zzzzzz
                where
                    OpenTime between '",Daty[1],"' and '",,"'
                and Symbol like '%xxx%'

When I do:

x <- sqlQuery(ch, query)

R is not able to import this table.

Upvotes: 0

Views: 86

Answers (1)

clemens
clemens

Reputation: 6813

The variable query must be a string. One way of including variables in the query is using paste0()

query <- paste0(
  "select
    Symbol
  , OpenTime
  from
  xxx t
  inner join
  zzz i
  on
  t.xxxxx = i.zzzzzz
  where
  OpenTime between '",
  Sys.Date(), # first date Daty[1] in your case
  "' and '",
  Sys.Date() + 1, # second date
  "' and Symbol like '%xxx%'"
)

This returns (using cat(query)):

select
    Symbol
  , OpenTime
  from
  xxx t
  inner join
  zzz i
  on
  t.xxxxx = i.zzzzzz
  where
  OpenTime between '2017-10-20' and '2017-10-21' and Symbol like '%xxx%'

Upvotes: 1

Related Questions