Converting character to class date

I have this query

sql <- "select 
    observationid::text,id,date, ST_X(location::geometry) as lon, ST_Y(location::geometry) as lat
    from test
    where date>=  '1850-01-01' and date  <'1851-01-01'"
datain<-dbGetQuery(dbh,sql)

that I am running from R. What I would like to do is to set date range outside the query with something like

datamin <- '1850-01-01'
datamax <- '1851-01-01'
sql <- "select 
    observationid::text,id,date, ST_X(location::geometry) as lon, ST_Y(location::geometry) as lat
    from test
    where date>=  datamin and date  <datamax"
datain<-dbGetQuery(dbh,sql)

but I get this error

Error in postgresqlExecStatement(conn, statement, ...) : 
  RS-DBI driver: (could not Retrieve the result : ERROR:  column "datamin" does not exist

Many thanks

Answers


You need to code datamin and datamax as dates:

datamin <- as.Date('1850-01-01')
datamax <- as.Date('1851-01-01')

datamin <- '1850-01-01'
datamax <- '1851-01-01'

sql <- sprintf("select 
        observationid::text,id,date, ST_X(location::geometry) as lon, ST_Y(location::geometry) as lat
        from test
        where date>= '%s' and date < '%s';",datamin,datamax)
datain<-dbGetQuery(dbh,sql)

Need Your Help

Error concatenating characters in swift

swift concatenation

I tried to concatenate two characters in Swift but it shows me the error

How do you change value of ExchangeConnectionMode (Outlook)

c# outlook outlook-redemption

I am writing a test case where I want to programatically set Outlook to be working offline and then test the reaction when outlook comes back online again. Is there anyway I can change the property