Cx_oracle: To_date with variables in python

Question

I am trying to do this

startdate = "20160123"
enddate = "20160204"
cmd = "select identification_number from bug where submitted_date >= TO_DATE(:1,'dd-MON-yy') and submitted_date <= TO_DATE(:2,'dd-MON-yy')"
cursor.execute(cmd,(startdate,enddate))

I get an error

cursor.execute(cmd,(sdate,edate))
cx_Oracle.DatabaseError: ORA-01861: literal does not match format string

I saw previous threads regarding this error but nothing solved my problem

What does :1 and :2 mean here? The to_date function expects an string as first parameter. — Utsav
– Utsav, Commented Mar 4, 2016 at 9:21
@Utsav - they are just positional bind variable placeholders. (There's an explanation here). — Alex Poole
– Alex Poole, Commented Mar 4, 2016 at 9:42

Utsav · Accepted Answer · 2016-03-04 09:53:27Z

2

I am not sure how startdate and enddate is translated to :1 and :2 but if it does, then the issue with date format. You are passing YYYYMMDD and you casting it as DD-MON-YYYY . Try to change it. Also you are missing from clause.

And I'd used between clause instead.

select identification_number 
from <your_table>
where 
submitted_date between 
TO_DATE(:1,'YYYYMMDD') and TO_DATE(:2,'YYYYMMDD')

If this works, then use same date format in your code

startdate = "20160123"
enddate = "20160204"
cmd = "select identification_number from <your_table> where submitted_date between TO_DATE(:1,'YYYYMMDD') and TO_DATE(:2,'YYYYMMDD')"

edited Mar 4, 2016 at 9:53

answered Mar 4, 2016 at 9:29

Utsav

8,1632 gold badges22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2471313 Over a year ago

Thanks a lot. I changed all the date formats to be of one specific form and it worked

jryan14ify Over a year ago

How do you use TO DATE() inside an executemany() statement?

Collectives™ on Stack Overflow

Cx_oracle: To_date with variables in python

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related