ERROR use datename/datepart in R sqldf package

Question

I have an error message when I running sqldf package for datename or datepart. Here is the table I used.

Height Date
163  12/01/90
182  11/13/88
167   5/14/97
172   3/18/94
170  10/11/92 
185   7/15/90


expected_table <-sqldf("select 
               [Height],
               (datename(weekday,[Date])) AS [Day of Week]
               from table1

               ")

Error in sqliteSendQuery(con, statement, bind.data) : 
  error in statement: no such column: weekday                     

If not,does it have any way to get weekday from the [Date]?

Answer 1

sqldf is a thin layer which passes the data and SQL statement to a back end of your choice. The default back end is sqlite but it also supports H2, PostgreSQL and MySQL. SQLite does not support datename but H2 does support dayname:

# reproducibly set up input data frame 
Lines <- "Height Date
163  12/01/1990
182  11/13/1988
167   5/14/1997
172   3/18/1994
170  10/11/1992 
185   7/15/1990"
DF <- read.table(text = Lines, header = TRUE)
DF$Date <- as.Date(DF$Date, "%m/%d/%Y")  # Date class

library(RH2)  # if RH2 is loaded sqldf will automatically use H2 instead of sqlite
library(sqldf)

sqldf("select *, dayname(Date) Weekday from DF")

giving:

  Height       Date   Weekday
1    163 1990-12-01  Saturday
2    182 1988-11-13    Sunday
3    167 1997-05-14 Wednesday
4    172 1994-03-18    Friday
5    170 1992-10-11    Sunday
6    185 1990-07-15    Sunday

Note: Of course it is also easy to do this in straight R:

transform(DF, Weekday = format(Date, "%A"))

giving:

  Height       Date   Weekday
1    163 1990-12-01  Saturday
2    182 1988-11-13    Sunday
3    167 1997-05-14 Wednesday
4    172 1994-03-18    Friday
5    170 1992-10-11    Sunday
6    185 1990-07-15    Sunday