search

tidyverse / dbplyr

Database (DBI) backend for dplyr
https://dbplyr.tidyverse.org
Other
477 stars 173 forks source link

Incorrect translation of dates with Oracle ODBC. #1468

Open edonnachie opened 8 months ago

edonnachie commented 8 months ago

When translating a date to SQL with an ODBC connection to an Oracle database, dbplyr returns SQL of the form "DATE 2024-02-27". This is incorrect and leads to the error "Failed to collect lazy table".

dbplyr should should translate o valid Oracle SQL of the form to_date('2024-2-07', 'YYYY-MM-DD')

This problem did not arise when using the ROracle package, which I, like Oracle, have now all but abandoned.

Example; With db as an odbc connection to an Oracle database (Formal class 'Oracle' [package "odbc"]:

 dbplyr::translate_sql(X > as.Date("2024-01-01"), con=db)
# Returns: <SQL> "X" > DATE 2024-01-01
Thranholm commented 8 months ago

I had a similar problem, the issue is fixed in #1389 and in the dev-version of the package.