Per the sqldf()
文档 https://github.com/ggrothendieck/sqldf#4-how-does-sqldf-work-with-date-class-variables,需要将日期格式化为数值,以便将它们作为日期处理。这可以通过以下方式完成sprintf()
生成 SQL 查询时。
SHV <- structure(list(field1 = c(1378L, 1653L, 1882L, 2400L,
2305L, 2051L, 2051L, 2051L, 1796L, 2054L, 2568L, 1290L, 1804L,
1804L, 3855L, 1297L, 2321L, 2321L, 2321L, 2071L, 2071L, 2074L,
2588L, 1567L, 1317L, 1317L, 808L, 808L, 1321L, 2350L, 1586L,
2613L, 1590L, 2614L, 2107L, 1340L, 1085L, 1085L, 2365L, 1344L,
1601L, 1858L, 1603L, 1603L, 1860L, 2376L, 1355L, 1867L, 2382L,
1872L), fieldDate = structure(c(12551, NA, NA, 14057, 15337,
12919, 13336, 10325, 14984, 15643, 12864, 11242, 10749, 11207,
10602, NA, 12646, 15649, NA, NA, NA, NA, NA, 17015, 13938, NA,
16693, NA, NA, 12634, 12614, 10689, 12755, 10844, 11375, 4899,
17298, 10905, 11450, NA, 10330, 15429, 12634, 10504, 12625, 11081,
10939, NA, 12934, 11176), class = "Date")), .Names = c("field1",
"fieldDate"), row.names = c(NA, 50L), class = "data.frame")
library(sqldf)
sqlStmt <- paste("select field1, fieldDate from SHV",
"where fieldDate between ",
sprintf("%d and %d",as.Date('2004-01-01','%Y-%m-%d'),
as.Date('2005-01-01','%Y-%m-%d')))
datedf<-sqldf(sqlStmt)
datedf
> datedf
field1 fieldDate
1 1378 2004-05-13
2 2321 2004-08-16
3 2350 2004-08-04
4 1586 2004-07-15
5 1590 2004-12-03
6 1603 2004-08-04
7 1860 2004-07-26
>
The sprintf()
语句将日期转换为数值,这确保了between
SQL 中的运算符工作正常。
> sqlStmt
[1] "select field1, fieldDate from SHV where fieldDate between 12418 and 12784"
>