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)
The problem seems to be in the second "from" where the "extract" function
is called. Does this need a fix or am I doing something wrong?
Thanks in advance and apologies if it turns out a simple error.
-M.L.
Using extract function for dates in sqldf
2 messages · Michael.Laviolette at dhhs.state.nh.us, 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