CODEWITHSUNDEEP
roracle-databaseoracle11g
Kinjal
Kinjal

Reputation: 666

Extract a table from the Oracle database using R

I am trying to extract a table from Oracle database using R by giving the date condition

Following is the code that I run in R:

pw14.db.train<-sqlQuery(myconn,"select * from r_input where snapshot_date <='16-01-2015'")

But I get the following error:

[1] "HY000 1843 [Oracle][ODBC][Ora]ORA-01843: not a valid month\n"                                     
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select * from r_input where snapshot_date <='16-01-2015'

The date format for snapshot_date col. on the database table is "23-07-14".

What should be the correct query that I need to write when using the date column?

Upvotes: 1

Views: 533

Answers (1)

LyzandeR
LyzandeR

Reputation: 37889

You need to convert the character string '16-01-2015' into date type since your column on Oracle is of date type:

pw14.db.train <- sqlQuery(myconn,"select * from r_input where snapshot_date <=  to_date('16-01-2015', 'dd-mm-yyyy') ")

and then your query will work correctly.

Upvotes: 1

Related Questions