I have extracted data to a temporary table in SQL Server using DBI::dbGetQuery
.
Even though, in the real query (not the play query below), I
select convert(date, date_value) as date_value
, the dates are still stored as character.
I then try to mutate the character representing the date using lubridate::ymd
, however I obtain a message saying
date_value not found
I have also tried, convert(date, date_value)
and as.Date
to no avail.
require(dplyr)
if (dbExistsTable(con, "##temp", catalog_name = "tempdb")){
dbRemoveTable(con, "##temp")
}
DBI::dbGetQuery(con, paste(
"select
convert(date, '2013-05-25') as date_value
into ##temp
"))
tbl(con, "##temp")
# Error - date_value not found
tbl(con, "##temp") %>% mutate(date_value= lubridate::ymd(date_value))
# this works
tbl(con, "##temp") %>% mutate(temp= date_value)
# this doesn't work - date value not found
tbl(con, "##temp") %>% mutate(temp= lubridate::ymd(date_value))
How can I work this field as a date?
Note: When I write the following in SQL Server, date_value shows as a date Type
select
convert(date, '2013-05-25') as date_value
into #hello
select *
from #hello
exec tempdb..sp_help #hello
in response to the comment from @Kevin Arseneau, the following image shows the results from executing a show_query()
Some months ago I was looking a solution for use lubridate
functions + dplyr
on PostgreSQL unsuccessfully. Accidentally I found the easy solution using DBMS functions directly on dbplyr
coding.
Sorry, I will use PostgreSQL example because I don't know about SQL server functions. In this example I will create a temporal table in a PostgreSQL DBMS, then I will compute a new column with the function to_date()
provided by PostgreSQL. The result is the date that was looking for:
# Easy example on postgreSQL
library(tidyverse)
library(dbplyr)
library(RPostgreSQL)
con <- dbConnect(PostgreSQL(),
dbname="postgre",
host="localhost",
port=5432,
user="user",
password="pass")
date <- data_frame(date_str = c("20180212", "20180213"))
dbWriteTable(con, "tmp", date, temporary = TRUE)
tbl(con, "tmp") %>%
# The DBMS function is used here
mutate(date = to_date(date_str, "YYYYMMDD")) %>%
# Finally, I collect the data from database to R session
collect()
#># A tibble: 2 x 3
#> row.names date_str date
#>* <chr> <chr> <date>
#>1 1 20180212 2018-02-12
#>2 2 20180213 2018-02-13
You can try with settings for SQL Server
, and the CAST()
function could convert your strings to date, as is explained in this answer. I hope this help you.
I hope someday dplyr/dbplyr
can translate the lubridate
functions into SQL
queries.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With