[R] Using extract function for dates in sqldf

[Gabor Grothendieck]

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