CODEWITHSUNDEEP
rsqldf
Surah Li
Surah Li

Reputation: 603

Problems with sqldf: cannot select on date

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])

Upvotes: 0

Views: 796

Answers (1)

jlhoward
jlhoward

Reputation: 59345

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).

Upvotes: 3

Related Questions