Error in `abs(df$dif_days)`: non-numeric argument to mathematical function

test_date_functions <- function(con, write_schema) {
if ("tmpdate" %in% listTables(con, write_schema)) {
DBI::dbRemoveTable(con, inSchema(schema = write_schema, table = "tmpdate", dbms = dbms(con)))
# uploading date types gives 'Numeric value out of range' error
dplyr::mutate(date1 = as.character(.data$date1), date2 = as.character(.data$date2)) %>%
inSchema(schema = write_schema, table = "tmpdate0", dbms = dbms(con)),
date_tbl <- dplyr::tbl(con, inSchema(schema = write_schema, table = "tmpdate0", dbms = dbms(con))) %>%
dplyr::mutate(date1 = as.Date(date1), date2 = as.Date(date2)) %>%
compute(temporary = FALSE, name = "tmpdate", overwrite = TRUE)
DBI::dbRemoveTable(con, inSchema(schema = write_schema, table = "tmpdate0", dbms = dbms(con)))
DBI::dbWriteTable(con, inSchema(schema = write_schema, table = "tmpdate", dbms = dbms(con)), date_df)
date_tbl <- dplyr::tbl(con, inSchema(schema = write_schema, table = "tmpdate", dbms = dbms(con)))
dplyr::mutate(date3 = !!dateadd("date1", 1, interval = "year")) %>%
dplyr::mutate(dif_years = !!datediff("date1", "date2", interval = "year")) %>%
dplyr::mutate(dif_days = !!datediff("date1", "date2", interval = "day")) %>%
dplyr::select("date1", "date2", "date3", "dif_years", "dif_days") %>%
dplyr::left_join(date_df, ., by = c("date1", "date2")) %>%
dplyr::mutate(dif_years = as.numeric(dif_years), dif_days = as.numeric(dif_days))
expect_true(all((lubridate::interval(df$date1, df$date3) / lubridate::years(1)) == 1))
expect_equal(df$dif_years, c(1, 1, 0, 0))
dplyr::mutate(date2 = !!dateadd("date1", 1, interval = "day")) %>%
dplyr::mutate(date3 = !!dateadd("date1", -1, interval = "day")) %>%
dplyr::mutate(dif_days2 = !!datediff("date1", "date2", interval = "day")) %>%
dplyr::mutate(dif_days3 = !!datediff("date1", "date3", interval = "day")) %>%
dplyr::mutate(dif_days2 = as.integer(dif_days2), dif_days3 = as.integer(dif_days3))
expect_true(all(lubridate::interval(df$date1, df$date2) / lubridate::days(1) == 1))
expect_true(all(lubridate::interval(df$date1, df$date3) / lubridate::days(1) == -1))
dplyr::mutate(number1 = 1L, minus1 = -1L) %>%
dplyr::mutate(date2 = !!dateadd("date1", "number1", interval = "day")) %>%
dplyr::mutate(date3 = !!dateadd("date1", "minus1", interval = "day")) %>%
# TODO paste0 translation incorrect on bigquery
test_that(glue::glue("{dbtype} - date functions"), {
if (!(dbtype %in% ciTestDbs)) skip_on_ci()
if (dbtype != "duckdb") skip_on_cran() else skip_if_not_installed("duckdb")
# TODO as.Date translation fails on Oracle. asDate provides the workaround.
test_that('dateadd works without pipe', {
DBI::dbWriteTable(con, "tbl", data.frame(date = as.Date("2020-01-01")))
dplyr::mutate(next_day = !!dateadd("date", 1, "day")) %>%
dplyr::mutate(next_day = !!dateadd("date", 1, "day")) |>
# dateadd does not work without the magrittr pipe
df3 <- dplyr::mutate(db, next_day = !!dateadd("date", 1, "day")) %>%
# expect_equal(test_translate_sql(difftime(start_date, end_date, units = "days")), sql("DATEDIFF(`end_date`, `start_date`)"))
# expect_equal(test_translate_sql(difftime(start_date, end_date)), sql("DATEDIFF(`end_date`, `start_date`)"))
# expect_equal(test_translate_sql(add_years(x, 1)), sql("ADD_MONTHS(`x`, 1.0 * 12.0)"))
# expect_equal(test_translate_sql(add_days(x, 1)), sql("DATE_ADD(`x`, 1.0)"))
# expect_equal(test_translate_sql(date_build(2020, 1, 1)), sql("MAKE_DATE(2020.0, 1.0, 1.0)"))
# expect_equal(test_translate_sql(date_build(year_column, 1L, 1L)), sql("MAKE_DATE(`year_column`, 1, 1)"))
# The development version of dbplyr contains translations for clock functions that we can use instead of the workarounds.
test_clock_functions <- function(con, write_schema) {
# date_df %>% dplyr::mutate(dif_days = as.integer(difftime(date2, date1)))
# Note there is an issue with uploading date types to Oracle
# add_years is not working on spark or duckdb
# add_days, difftime does not work on duckdb
# add_years and add_days return datetimes on postgres making the as.Date() conversion is needed
# date_build does not work on redshift or duckdb https://github.com/tidyverse/dbplyr/pull/1513
# on postgres we need the as.Date conversion around add_years and add_days
# clock::add_years does not work on spark. must use add_days
df <- dplyr::mutate(df, date3 = as.Date(clock::add_days(date1, 1L*365L)))
if (!(dbms(con) %in% c("duckdb", "redshift"))) {
# if (dbms(con) %in% c("redshift", "postgres")) {
df$dif_days <- abs(df$dif_days) # TODO on some dbms the translation of difftime does not match base R difftime and we get the wrong sign
# some of these translations are failing on certain database platforms
expect_equal(sort(df$dif_days), sort(c(365, 366, 364, 31))) # row order is not preserved on snowflake
if (!(dbms(con) %in% c("spark", "duckdb"))) expect_equal(unique(df$date7), as.Date("2010-12-01")) # fails on spark
if (dbtype != "bigquery") test_clock_functions(con, write_schema)
test_as.Date <- function(con, cdm_schema, write_schema) {
df <- df %>% dplyr::mutate(string = CONCAT(
df <- df %>% dplyr::mutate(string = paste(
skip_if(any(cdm_schema == "", write_schema == ""))

# TODO on some dbms the translation of difftime does not match base R difftime and we get the wrong sign
# row order is not preserved on snowflake