Filter datetime in Oracle through dbplyr

user_9684935 0
Viewed 297

I am trying to filter a datetime field in an Oracle database through dbplyr. I have tried numerous different ways of doing it, my two best guesses can be seen below, with the error messages. Anyone knows how to do this?

tbl(con2, in_schema("DB", "TABLE")) %>%
  filter(FULL_DATE >= "2020-09-01 00:00:00")

gives me:

Error: nanodbc/nanodbc.cpp:1617: HY000: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string
 
<SQL> 'SELECT * FROM (SELECT *
FROM (DB.TABLE) 
WHERE ("FULL_DATE" >= '2020-09-01 00:00:00')) "zzz90" WHERE ROWNUM <= 11.0'

and

tbl(con2, in_schema("DB", "TABLE")) %>%
    filter(sql('CREATED_DATE >= 2020-09-01 00:00:00'))

gives me:

Error in result_fetch(res@ptr, n) : 
  Query needs to be bound before fetching
dbplyr r datetime oracle
1 Answers

Turned out this solution worked:

tbl(con2, in_schema("DB", "TABLE")) %>%
  filter(FULL_DATE >= to_date("2020-09-01", "YYYY:MM:DD"))
user_96849350
Related