odbc-date-filtering.R

library(DBI)
library(dbplyr)
library(dplyr, warn.conflicts = FALSE)

# This is an Access connection
cn <- dbConnect(odbc::odbc(), dsn = "dbplyr-testing")

# Connect to the test table
test_tbl <- tbl(cn, "test-1")

# Look at the table
# In Access, datefield is a Date/Time field
test_tbl
#> Warning in as.POSIXlt.POSIXct(x, tz): unable to identify current timezone 'C':
#> please set environment variable 'TZ'
#> # Source:   table<test-1> [?? x 3]
#> # Database: ACCESS
#> #   14.00.0000[admin@ACCESS/C:\Users\Administrator\Desktop\R\dbplyr-testing\dplyr-test-db.accdb]
#>      ID  datefield Field1
#>   <int>     <dttm>  <chr>
#> 1     1 2017-07-19     hi
#> 2     2 2017-07-20  hello
#> 3     3 2017-07-21   test
#> 4     4 2017-07-22   this


# This doesn't work. Needs to be a date
test_tbl %>%
  filter(datefield > "2017-07-20")
#> Error: <SQL> 'SELECT TOP 10 *
#> FROM `test-1`
#> WHERE (`datefield` > '2017-07-20')'
#>   nanodbc/nanodbc.cpp:1587: 22018: [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

# This works fine!
tbl_filter <- test_tbl %>%
  filter(datefield > as.Date("2017-07-20"))

tbl_filter %>% show_query()
#> <SQL>
#> SELECT *
#> FROM `test-1`
#> WHERE (`datefield` > CDate('2017-07-20'))

tbl_filter
#> # Source:   lazy query [?? x 3]
#> # Database: ACCESS
#> #   14.00.0000[admin@ACCESS/C:\Users\Administrator\Desktop\R\dbplyr-testing\dplyr-test-db.accdb]
#>      ID  datefield Field1
#>   <int>     <dttm>  <chr>
#> 1     3 2017-07-21   test
#> 2     4 2017-07-22   this

# Using the Access function DatePart
# As suggested by Edgar
# This extracts the weekday, month, day, year
tbl_dates <- test_tbl %>%
  mutate(weekday = DatePart("w", datefield),
         month   = DatePart("m", datefield),
         day     = DatePart("d", datefield),
         year    = DatePart("yyyy", datefield))

tbl_dates %>% show_query()
#> <SQL>
#> SELECT `ID`, `datefield`, `Field1`, DATEPART('w', `datefield`) AS `weekday`, DATEPART('m', `datefield`) AS `month`, DATEPART('d', `datefield`) AS `day`, DATEPART('yyyy', `datefield`) AS `year`
#> FROM `test-1`

tbl_dates
#> # Source:   lazy query [?? x 7]
#> # Database: ACCESS
#> #   14.00.0000[admin@ACCESS/C:\Users\Administrator\Desktop\R\dbplyr-testing\dplyr-test-db.accdb]
#>      ID  datefield Field1 weekday month   day  year
#>   <int>     <dttm>  <chr>   <int> <int> <int> <int>
#> 1     1 2017-07-19     hi       4     7    19  2017
#> 2     2 2017-07-20  hello       5     7    20  2017
#> 3     3 2017-07-21   test       6     7    21  2017
#> 4     4 2017-07-22   this       7     7    22  2017


# Directly filtering with DatePart works too
# A 5 for weekday is a Friday, let's only select those
test_tbl %>%
  filter(DatePart("w", datefield) == 5)
#> # Source:   lazy query [?? x 3]
#> # Database: ACCESS
#> #   14.00.0000[admin@ACCESS/C:\Users\Administrator\Desktop\R\dbplyr-testing\dplyr-test-db.accdb]
#>      ID  datefield Field1
#>   <int>     <dttm>  <chr>
#> 1     2 2017-07-20  hello

# One last example
# Grab the month name by first extracting the month as an integer
# Then use Access SQL's MonthName function
test_tbl %>%
  mutate(month_name = MonthName(DatePart("m", datefield)))
#> # Source:   lazy query [?? x 4]
#> # Database: ACCESS
#> #   14.00.0000[admin@ACCESS/C:\Users\Administrator\Desktop\R\dbplyr-testing\dplyr-test-db.accdb]
#>      ID  datefield Field1 month_name
#>   <int>     <dttm>  <chr>      <chr>
#> 1     1 2017-07-19     hi       July
#> 2     2 2017-07-20  hello       July
#> 3     3 2017-07-21   test       July
#> 4     4 2017-07-22   this       July