R, issue with sqldf: cannot make condition on date

Question

I have a R dataframe with a field date (type date), i want to query this dataframe using sqldf library, but the where condition doesn't seem to work on the date field.

The query I'm using is:

sqldf("select * from  elog where 
      date >= '1997-01-01'
      limit 6")

It returns me an empty dataframe even though 'elog' has lines having 1997-01-01 as date

Answer 1

You could try the same command after loading library(RH2)

library(RH2)
library(sqldf)
sqldf("select * from elog where
         date >= '1997-01-01' 
         limit 6")
#        date
#1 1997-01-01
#2 1997-07-01
#3 1998-01-01
#4 1998-07-01
#5 1999-01-01
#6 1999-07-01

Or without it, you may need to feed the numeric value as @bergant indicated in the comments

as.numeric(as.Date('1997-01-01'))
#[1] 9862
 sqldf("select * from elog
        where date >= 9862
        limit 6")
 #       date
 #1 1997-01-01
 #2 1997-07-01
 #3 1998-01-01
 #4 1998-07-01
 #5 1999-01-01
 #6 1999-07-01

the same using sprintf (@bergant's code)

 sqldf(sprintf("select * from elog 
       where date >= %d limit 6",
        as.Date('1997-01-01')))
 #       date
 #1 1997-01-01
 #2 1997-07-01
 #3 1998-01-01
 #4 1998-07-01
 #5 1999-01-01
 #6 1999-07-01

data

set.seed(42)
elog <- data.frame(date = seq(as.Date('1996-01-01'), 
           length.out=20, by='6 month', value=rnorm(20)) )

Answer 2

Another similar alternative:

elog$newdate <- as.character(elog$date)
sqldf("select date from elog where
         newdate >= '1997-01-01' 
         limit 6")