Search code examples
roracle-databasedater-dbi

Convert R Date to POSIXct Date in Database

I want to use dbGetQuery to pull data from a database:

date <- as.Date("2020-01-01")
string <- paste("select * from D where D_DATE >= '", date, "'", sep="")
D <- dbGetQuery(conn, string)

But the dates D_DATE in D have type "POSIXct": How can I fix my query?

Setting date <- as.POSIXct(date) didn't work

Solution

  • Try to pass date as string, then convert to date in SQL, something like:

    date <- "2020-01-01"

string <- paste0("SELECT * FROM D WHERE D_DATE >= TO_DATE('", date, "', 'YYYY-MM-DD')")
string
# [1] "SELECT * FROM D WHERE D_DATE >= TO_DATE('2020-01-01', 'YYYY-MM-DD')"

    We might need to truncate the D_DATE: TRUNC(D_DATE)