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.

Littlefoot · Accepted Answer · 2021-12-06 21:26:20Z

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)

answered Dec 6, 2021 at 21:26

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

1 Comment

Robby star Over a year ago

to_date('{run_dt}','YYYY-MM-DD') as max_upload_date, --- that how i used and it worked. thanks

Collectives™ on Stack Overflow

Date issue - python to oracle sql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related