Testing out dbplyr and connection to database and am getting dates returned as a double
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
df.in <- data.frame(count = c(1:2),Date = as.Date(rep(0,2), origin = "1900-
01-01"),stringsAsFactors = FALSE)
str(df.in)
# 'data.frame': 2 obs. of 2 variables:
# $ count: int 1 2
# $ Date : Date, format: "1900-01-01" "1900-01-01"
DBI::dbWriteTable(con, "df.in", df.in, overwrite=TRUE)
df.out<- dplyr::tbl(con, "df.in")
str(df.out)
# List of 2
# $ src:List of 2
# ..$ con :Formal class 'SQLiteConnection' [package "RSQLite"] with 6
slots
# .. .. ..@ ptr :<externalptr>
# .. .. ..@ dbname : chr ":memory:"
# .. .. ..@ loadable.extensions: logi TRUE
# .. .. ..@ flags : int 70
# .. .. ..@ vfs : chr ""
# .. .. ..@ ref :<environment: 0x00000000137b7dc0>
# ..$ disco: NULL
# ..- attr(*, "class")= chr [1:3] "src_dbi" "src_sql" "src"
# $ ops:List of 2
# ..$ x :Classes 'ident', 'character' chr "df.in"
# ..$ vars: chr [1:2] "count" "Date"
# ..- attr(*, "class")= chr [1:3] "op_base_remote" "op_base" "op"
# - attr(*, "class")= chr [1:4] "tbl_dbi" "tbl_sql" "tbl_lazy" "tbl"
df.out
# Source: table<df.in> [?? x 2]
# Database: sqlite 3.19.3 [:memory:]
# count Date
# <int> <dbl>
# 1 1 -25567
# 2 2 -25567
a) df.out is a list. What is the best way to see the underlying data being returned i.e. count and Date in a data.frame or tbl format
b) Why is a double being returned rather than a date
c) As I am hitting this problem , I have been unable to replicate my initial issue (with an MS SQL server connection) which was that when using dplyr code on data - where gameDate has been confirmed as a Date field - to do a mutation I get this error
df.out %>%
mutate(month=months(gameDate))
# nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][SQL Server Native Client
11.0][SQL Server]'MONTHS' is not a recognized built-in function name.
Is there anyway around this. I thought dbplyr translated the dplyr code into appropriate SQL
TIA for help on any of above points
this is because, as of today, the months() vector function does not have a translation for MSSQL in dbplyr yet. The nice part how dplyr translations work is that it will let you call a database native command, in MSSQL, the DATENAME function should do what you need. This code should work:
df.out %>%
mutate(month=datename(month, gameDate))