0

I'm trying to use native sql functions within sqldf such as month. I am not sure why this doesn't work. There is monthly data in the dataframe which I am pulling from so I don't understand. If I subset it in dbGetQuery, then it works since it is pulling directly from the MySQL database, but when I try to do the same with the sqldf package it fails. 

sqldf("select * from data_old_cust where month(order_date) = 1", drv = 'SQLite')

Error in sqliteSendQuery(con, statement, bind.data) : error in statement: no such function: month

thelatemail
  • 91,185
  • 12
  • 128
  • 188
Hidden Markov Model
  • 129
  • 4

1 Answers1

2

By default sqldf uses SQLite via the RSQLite driver R package and month is not a function supported by SQLite nor is it one of the extension functions supported by RSQLite. There are some examples of processing dates on the sqldf home page https://github.com/ggrothendieck/sqldf and there is information on SQLite date functions here: https://www.sqlite.org/lang_datefunc.html and from these resources you can find other functions.

If you use sqldf with the H2 database supported by the RH2 R package then month is supported. For example,

library(RH2)
library(sqldf)
DF <- data.frame(d = as.Date("2000-08-15"))

sqldf("select month(d) from DF")

giving:

   MONTH("d")
 1          8
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341