CODEWITHSUNDEEP
sqlroracle-database
djc55
djc55

Reputation: 555

Using Where Clause with Dates When Reading SQL Query in R

I am attempting to query an Oracle datebase from R using the SQL function found here.

When I complete an easy query, such as 

'SELECT * FROM TABLE_1'

the query executes fine. However, when I add a conditional date statement to the query, such as

'SELECT * FROM TABLE_1 WHERE START_DT BETWEEN '01-JUL-2018' AND '30-JUN-2019'

I get the following error message:

Error in .oci.SendQuery(conn, statement, data = data, prefetch = prefetch, : ORA-00904: "30-JUN-2019": invalid identifier

Any idea how I can fix this?

Upvotes: 0

Views: 453

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

The exact error appears to be that you didn't escape the single quotes you placed around the date literals in your R query string. But, fixing that still leaves the problem that your date literals are invalid for Oracle. I recommend using this:

sql <- "SELECT * FROM TABLE_1 WHERE START_DT BETWEEN DATE '2018-07-01' AND DATE '2019-06-30'"

You could also use the TO_DATE function, e.g. TO_DATE('01-JUL-2018', 'DD-MON-YYYY'), but this is a bit harder to read than using the DATE keyword.

Upvotes: 2

Related Questions