[R] Using extract function for dates in sqldf

Gabor Grothendieck ggrothendieck at gmail.com
Wed Mar 21 21:58:29 CET 2012

On Wed, Mar 21, 2012 at 11:31 AM,  <Michael.Laviolette at dhhs.state.nh.us> wrote:
> I'm trying to use sqldf to query for the earliest date of a blood test when
> patients have had multiple tests in a given year. My query looks like this:
> test11 <- sqldf("select CHILD_ID, min(SAMP_DATE)
>                 from lab
>                 group by CHILD_ID
>                 having extract (year from SAMP_DATE) = 2011")
> SAMP_DATE has class "date." I get the error message
> Error in sqliteExecStatement(con, statement, bind.data) :
>  RS-DBI driver: (error in statement: near "from": syntax error)

extract is not supported by sqlite.  Check the SQLite date and time
functions link on the left side of the sqldf home page.  (The other
three databases supported by sqldf all do support extract.)

Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

More information about the R-help mailing list