0

The problem is similar with this one (R, issue with sqldf: cannot make condition on date) but with different error term:

Suppose I have a table:

OrderDate  Sales
2005-02-28  12
2005-02-28  234
...

In the original R DataFrame, the data type of OrderDate is double (after as.Date command)

I want to do something like select * from table where OrderDate='2005-02-28' but encounter errors:

Error: unexpected numeric constant in "sqldf('select * from table where OrderDate='2005"

I tried some solutions proposed on the listed question and others, but still cannot solve it.

I feel like there's something wrong with the data type of OrderDate but don't know exactly. i.e. when I find the numerical value of the date and feed into my condition, it encounters such an error (x is the accordingly numerical value):

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", : Unable to retrieve JDBC result set for select * from table where OrderDate=x order by Qty desc limit 3 (Cannot parse "DATE" constant "x"; SQL statement: select * from table where OrderDate=x order by Qty desc limit 3 [22007-175])

Community
  • 1
  • 1
Surah Li
  • 573
  • 1
  • 4
  • 6
  • 1
    `"sqldf('select * from table where OrderDate='2005"` <- single quote just before select, and single quote just before 2005, the `sqldf` get a string ending at `=` followed by an integer and can't guess what you try to do. Escape the single quote withing the query. For your second case I assume you didn't build the qury string with paste for so x is replaced by it's values, and obvisouly "x" is a character and not a date string nor a number. – Tensibai Sep 04 '15 at 08:07
  • 1
    The SQL statement shown in the question would work if `OrderDate` were character using the H2 back end. If `OrderDate` is of `"Date"` class and assuming you are using the H2 backend the code in the question is trying to compare date and char types. Try this to ensure that the comparison is between the same types: `sqldf("select * from table where OrderDate= DATE '2005-02-28' ")` – G. Grothendieck Sep 04 '15 at 16:12

1 Answers1

3

sqldf, like R, stores dates as the number of days since 1970-01-01; see FAQ 4 in this post for more detail.

This will work:

df <- read.table(header=TRUE, text="OrderDate  Sales
2005-02-28  12
2005-02-28  234
2005-03-01  567
2005-03-03  890")
df$OrderDate <- as.Date(df$OrderDate)

library(sqldf)
sqldf(sprintf("select * from df where OrderDate=%i",as.Date("2005-02-28")))
#    OrderDate Sales
# 1 2005-02-28    12
# 2 2005-02-28   234

Also this:

start <- as.Date("2005-02-28")
end   <- as.Date("2005-03-01")
sqldf(sprintf("select * from df where OrderDate between %i and %i",start,end))
#    OrderDate Sales
# 1 2005-02-28    12
# 2 2005-02-28   234
# 3 2005-03-01   567

Note the use of %i so the argument is interpreted as an integer (you could use %d as well).

jlhoward
  • 58,004
  • 7
  • 97
  • 140