show clock::date_build with dplyr::coalesce

[edward-burn]

when supported with duckdb duckdb/duckdb-r#643, we can show how to use clock::date_build and dplyr::coalesce

library(duckdb)
#> Loading required package: DBI
library(dbplyr)
lazy_frame(year_var = 2000,
           month_var = 10,
           day_var = NA,
           con = simulate_postgres()) |>
  dplyr::mutate(
    new_date = clock::date_build(year = year_var, 
                                 month = dplyr::coalesce(month_var, 1), 
                                 day = dplyr::coalesce(day_var, 1))
  )
#> <SQL>
#> SELECT
#>   `df`.*,
#>   MAKE_DATE(`year_var`, COALESCE(`month_var`, 1.0), COALESCE(`day_var`, 1.0)) AS `new_date`
#> FROM `df`

lazy_frame(year_var = 2000,
           month_var = 10,
           day_var = NA,
           con = simulate_mssql()) |>
  dplyr::mutate(
    new_date = clock::date_build(year = year_var, 
                                 month = dplyr::coalesce(month_var, 1), 
                                 day = dplyr::coalesce(day_var, 1))
  )
#> <SQL>
#> SELECT
#>   `df`.*,
#>   DATEFROMPARTS(`year_var`, COALESCE(`month_var`, 1.0), COALESCE(`day_var`, 1.0)) AS `new_date`
#> FROM `df`

lazy_frame(year_var = 2000,
           month_var = 10,
           day_var = NA,
           con = simulate_duckdb()) |>
  dplyr::mutate(
    new_date = clock::date_build(year = year_var, 
                                 month = dplyr::coalesce(month_var, 1), 
                                 day = dplyr::coalesce(day_var, 1))
  )
#> Error in `clock::date_build()`:
#> ! No known SQL translation

^{Created on 2026-02-08 with reprex v2.1.1}