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

Question

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?

David Klotz · Accepted Answer · 2019-06-10 17:38:24Z

1

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

answered Jun 10, 2019 at 17:38

David Klotz

2,4411 gold badge9 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Cooley Over a year ago

Hmm ... still getting an error: Error in enexpr(x) : object 'Date' not found

David Klotz Over a year ago

I get that error if I do !!round_month(Date) Not with !!round_month("Date")

stevec · Accepted Answer · 2022-02-04 15:37:30Z

0

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)"))

answered Feb 4, 2022 at 15:37

stevec

55.2k52 gold badges314 silver badges436 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related