R, issue with sqldf: cannot make condition on date -

i have r dataframe field date (type date), want query dataframe using sqldf library, condition doesn't seem work on date field.

the query i'm using is:

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

it returns me empty dataframe though 'elog' has lines having 1997-01-01 date

you try same command after loading library(rh2)

library(rh2) library(sqldf) sqldf("select * elog          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, may need feed numeric value @bergant indicated in comments

as.numeric(as.date('1997-01-01')) #[1] 9862  sqldf("select * elog         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 * elog         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)) )