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, Mar 04 '16 at 09:21
@Utsav - they are just positional bind variable placeholders. (There's an explanation [here](http://stackoverflow.com/a/33882805/266304)). — Alex Poole, Mar 04 '16 at 09:42

Utsav · Accepted Answer · 2016-03-04T09:53:27.850

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 04 '16 at 09:53

answered Mar 04 '16 at 09:29

Utsav

7,914
2
17
38

Thanks a lot. I changed all the date formats to be of one specific form and it worked – user2471313 Mar 04 '16 at 10:05
How do you use `TO DATE()` inside an `executemany()` statement? – jryan14ify Aug 18 '22 at 15:34

Cx_oracle: To_date with variables in python

1 Answers1