CODEWITHSUNDEEP
roracle-databasedater-dbi
Claudio Moneo
Claudio Moneo

Reputation: 569

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

Upvotes: 1

Views: 231

Answers (1)

zx8754
zx8754

Reputation: 56149

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)

Upvotes: 1

Related Questions