SQL tanslations for datediff in dbplyr?

tidyverse
,
1

I am using a MSSQL database and trying to do a datediff on a table.

my_tbl is a table reference tbl(con, in_schema('foo', 'dbo.table'))

my_tbl %>%
   select(`DOCUMENT STATUS`,  `PAYMENT AMOUNT`, `PAYMENT DATE`, `INVOICE DATE`) %>%
  mutate(invoice_age = DATEDIFF(`INVOICE DATE`, `PAYMENT DATE`)) %>%
  top_n(100)

This doesn't get translated to DATEDIFF().. I tried to use DATADIFF(day, INVOICE DATE, PAYMENT DATE) and that doesn't work either.

Any idea how to get this to translate to the correct sql statement?

1 Like
2

Yeah, that's not a "known function" from the dplyr/dbplyr standpoint yet.

From dbplyr - SQL translation

KNOWN FUNCTIONS

dplyr knows how to convert the following R functions to SQL:

  • basic math operators: + , - , * , / , %% , ^
  • math functions: abs , acos , acosh , asin , asinh , atan , atan2 , atanh , ceiling , cos , cosh , cot , coth , exp , floor , log , log10 , round , sign , sin , sinh , sqrt , tan , tanh
  • logical comparisons: < , <= , != , >= , > , == , %in%
  • boolean operations: & , && , | , || , ! , xor
  • basic aggregations: mean , sum , min , max , sd , var
  • string functions: tolower , toupper , trimws , nchar , substr
  • coerce types: as.numeric , as.integer , as.character
3

I think you're trying to use the SQL passthrough feature of dbplyr, which is great! Are you sure you have the syntax of DATEDIFF() correct?

This tells me you need to put the interval as the first arg.
https://www.w3schools.com/sql/func_sqlserver_datediff.asp

So I'd imagine something like the below being correct, but passing through the literal "day" could be a challenge.

DATEDIFF(day, `INVOICE DATE`, `PAYMENT DATE`)

Can you use the above version and add show_query() to the end of your pipe chain and show us the result?

4 Likes
4

I found a trick to make it work.

mutate(day = "DAY", invoice_age_days = DATEDIFF(DAY, `INVOICE DATE`, GETDATE())) %>%
      select(-day) %>%
4 Likes
5

Hi there! If your question's been answered (even by yourself!), would you mind choosing a solution? It helps other people see which questions still need help, or find solutions if they have similar problems. Here's how to do it.

6

Found a better way!

Previous solution:

mutate(day = "DAY", invoice_age_days = DATEDIFF(DAY, `INVOICE DATE`, GETDATE())) %>%
      select(-day) %>%

Better solution

mutate(invoice_age_days = DATEDIFF(sql("DAY"), `INVOICE DATE`, GETDATE()))

ref:

New solution does not require a dummy value.

1 Like
7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.