Insert Python datetime to Oracle column of type DATE

Question

I am trying to store a Python datetime object in an ORACLE column of type date.

so far, I have used,

rpt_time = time.strftime('%Y-%m-%d %H:%M:%S') or
rpt_time = str(datetime.datetime.now())

but all are giving ORA-01843: not a valid month

I am really confused how to insert ORACLE date type python datetime object

Lelandleler

Please post your complete Python code. – Computation

Answer 1

As far as my search shows, ORACLE can be picky on dates so this might be what you need to do.

Firstly, check the format of date you have. For example, if you have something like, 2010/01/26:11:00:00AM, then you might want to do following on your cursor execute:

insert into x
values(99, to_date('2010/01/26:11:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

Supernatant

Yes, giving an explicit format to to_date is the sure-fire way to insert dates. ORMs do the same thing, last time I checked. – Brazen

Answer 2

cx_Oracle supports passing objects of class datetime.datetime. Because of this when you already have object of this class (for example when calling datetime.datetime.now()) you should not try to format it and pass as a string but instead pass it directly. This way you prevent all errors caused by wrong format of date and/or time.

Example:

cursor.execute("INSERT INTO SomeTable VALUES(:now)", {'now': datetime.datetime.now()})

Be aware that you have to take additional steps if you want to prevent truncation of fractional part of seconds. For details please read Mastering Oracle+Python, Part 2: Working with Times and Dates article by Przemysław Piotrowski.

Objectify

Thanks a ton buddy! You saved me. Wasted 2 hours trying to workaround this! – Eslinger

Answer 3

You have to convert date from python to oracle by setting nls_date_format in you session

>>> rpt_time = time.strftime('%Y-%m-%d %H:%M:%S')
>>> rpt_time
'2014-05-12 21:06:40'

Then before inserting into oracle, do the following

cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'")

Windproof

Answer 4

datetime.now() in python gives you milliseconds and have to get rid of it before sucessfully writing to Oracle.

from datetime import datetime
....
cursor.execute("INSERT INTO myTable VALUES(to_date('" + str(datetime.now().replace(microsecond=0)) + "','yyyy-mm-dd hh24:mi:ss'))")
....

Crayfish