Date issue - python to oracle sql

Question

I have a run_dt that is creating issue while creating a table, the column data type changes on it own as Char.

print(execution_dt)
2020-10-05 14:24:25.352317    

run_dt= execution_dt.date()


print(run_dt)
2020-10-05

run_dt=str(run_dt)
print(run_dt)
2020-10-05

type(run_dt)
<class 'str'>

I have Oracle db as a backend and python for scripting.

cursor.execute(f'''create table taxes
select age,
   name,
   '{run_dt}' as max_upload_date,
    from Employee a left outer join code cd on (s.emp_id = cd.code)
    and s.transaction_date = {repr(run_dt)}
''')

if i execute the above python code, it will create a table taxes, but then the max_upload_date - column datatype becomes - Char automatically.

if i apply to_date function as per below:

to_date('{run_dt}')  as max_upload_date,

I get the below error:

error - cx_Oracle.DatabaseError: ORA-01861: literal does not match format string

please help. Thanks in Advance.

Answer 1

TO_DATE should be OK, but only if you apply appropriate format mask (instead of letting Oracle guess what to do; as you can see, it failed):

to_date('2020-10-05', 'yyyy-mm-dd')

(if that's what 10 and 05 represent; could be vice versa. I can't tell, but you should)