Open ablack3 opened 1 month ago
The translation of date_build is slightly incorrect on redshift.
date_build
https://github.com/tidyverse/dbplyr/blob/8f2fcb0852994afe11cc6792ef59ef5ffda84936/tests/testthat/test-backend-redshift.R#L66
con <- DBI::dbConnect(RPostgres::Redshift(), dbname = Sys.getenv("CDM5_REDSHIFT_DBNAME"), host = Sys.getenv("CDM5_REDSHIFT_HOST"), port = Sys.getenv("CDM5_REDSHIFT_PORT"), user = Sys.getenv("CDM5_REDSHIFT_USER"), password = Sys.getenv("CDM5_REDSHIFT_PASSWORD")) # current translation sql <- "SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT)), 'YYYY-MM-DD') AS dt" cat(sql) #> SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT)), 'YYYY-MM-DD') AS dt DBI::dbGetQuery(con, sql) #> Error: Failed to prepare query : ERROR: syntax error at or near "CAST" #> LINE 1: SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' CAST(1.0 AS TEXT)... #> ^ # corrected sql sql <- "SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' || CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT), 'YYYY-MM-DD') AS dt" cat(sql) #> SELECT TO_DATE(CAST(2020.0 AS TEXT) || '-' || CAST(1.0 AS TEXT) || '-' || CAST(1.0 AS TEXT), 'YYYY-MM-DD') AS dt DBI::dbGetQuery(con, sql) #> dt #> 1 2020-01-01 DBI::dbDisconnect(con)
Created on 2024-06-05 with reprex v2.1.0
The translation of
date_build
is slightly incorrect on redshift.https://github.com/tidyverse/dbplyr/blob/8f2fcb0852994afe11cc6792ef59ef5ffda84936/tests/testthat/test-backend-redshift.R#L66
Created on 2024-06-05 with reprex v2.1.0