Insert Python datetime to Oracle column of type DATE

24,615

Solution 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'));

Solution 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.

Solution 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'")
Share:
24,615
Alok Agarwal
Author by

Alok Agarwal

A passionate computer programmer working in Bangalore based start up working in Python using django, Phonegap javaScript and Jquery.

Updated on July 16, 2022

Comments

  • Alok Agarwal
    Alok Agarwal almost 2 years

    I am trying to store python datetime object to 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 montn

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