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 David Klotz · Accepted Answer · 2019-06-10T17:38:24

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

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

1 Answers