CODEWITHSUNDEEP
rsql-serverdbplyr
Aaron Cooley
Aaron Cooley

Reputation: 458

How to create custom SQL functions with R code in dbplyr?

I am using dbplyr to query an MSSQL database, and frequently round dates to the first of the month using mutate(YM = DATEFROMPARTS(YEAR(Date), MONTH(Date), 1)). I would like to be able to create an R function that will simplify this for me, e.g. type mutate(YM = round_month(Date)) and have dbplyr translate that to the DATEFROMPARTS function above.

It seems like I could do this using the sql_expr() function in dbplyr. Reading through the package's R code, it seems like that is how R functions are translated. For example:

year = function(x) sql_expr(DATEPART(YEAR, !!x))

See: https://github.com/tidyverse/dbplyr/blob/master/R/backend-mssql.R

So ... I tried to do the same thing on my own:

round_month <- function(x) sql_expr(DATEFROMPARTS(YEAR(!!x), MONTH(!!x), 1))

mytbl %>%
  mutate(YM = round_month(Date)) %>%
  show_query()

I expected to get this:

<SQL>
SELECT DATEFROMPARTS(YEAR("Date"), MONTH("Date"), 1) AS YM
FROM mytbl

But instead I get this:

<SQL>
SELECT round_month("Date") AS YM
FROM mytbl

Which obviously does nothing. What am I doing wrong?

Upvotes: 7

Views: 642

Answers (2)

stevec
stevec

Reputation: 52268

I'm not exactly sure what's going wrong in that specific case. But each SQL variant is slightly different so if you can find out the exact SQL code necessary to produce the result you want, then it's just a matter of providing that raw SQL to the sql() function.

For example, on any database backend that accepts ANSI SQL, this will extract the week from a date (note that the lubridate equivalent unfortunately doesn't play nice with dbplyr).

your_dbplyr_object %>%
  mutate(week = sql("EXTRACT(WEEK FROM meeting_date)"))

Upvotes: 0

David Klotz
David Klotz

Reputation: 2431

Try this out:

mytbl %>%
  mutate(YM = !!round_month("Date")) %>%
  show_query()

Based on this response: https://community.rstudio.com/t/how-to-subtract-dates-using-dbplyr/22135/5

Upvotes: 1

Related Questions