sqlserver datetime to oracle timestamp problems

Question

So I am working on a project to move data from a sql server db to an oracle db and the dates are giving me a hard time. To do data movement I have a python script that pulls data from the sql server db and and then runs an insert query.

The problem is that the dates look like this 2016-06-01 05:45:06.003 but if I drop the fractional seconds It will violate the primary key as there are plenty of records at 2016-06-01 05:45:06 but only one at 2016-06-01 05:45:06.003 so it must have milliseconds in it.

I should mention that data type for the column in oracle is TIMESTAMP

If I just pull the records and run my insert Which is just a basic insert with the values like this VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)

Doing that gets me dates that look like this 01-JUN-16 07.05.41.000000000 AM. This would be fine but for every single record the fractional seconds are 000000 so I have lost accuracy and this makes me violate the primary key.

I figured the fix would be to be explicit about the format so I modified the insert statement values clause to be like this VALUES(TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS,FF9'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15). This is even worse as it changes all the dates in every row to 16-JUN-20 12.00.00.000000000 AM I also tried YYYY-MM-DD HH24:MI:SS,FF9 but this yields the same results.

I am sure this something stupid that I am missing but for the life of me I don't know what it is.

Since asked, The data type of the column on the sql server table is DateTime and on the oracle server i have it currently set to TimeStamp(3) but I can change the data type on the oracle side.

I am using cx_oracle to connect and run the insert. I can confirm that before the the insert is run the data does have milliseconds.

The python:

def RunQuery():
    srccrsr.execute(query)
    return srccrsr.fetchall()

def RunQuery():
    srccrsr.execute(query)
    return srccrsr.fetchall()

def BuildBindList(recordsToWrite):
    closingRecords = []
    for rec in recordsToWrite:
        closingRecords.append((rec[0], rec[1], rec[2], rec[3], rec[4], rec[5], rec[6], rec[7], rec[8], rec[9], rec[10], rec[11], rec[12], rec[13], rec[14]))
    return closingRecords   

def write_to_table(recordsToWrite):  
    SQL = """INSERT INTO TRACE (DATETIME, ID, TZ, DOMAINID, EVENTNAME, REASONCODE, TARGETID, STATE, KEY, PERIPHERALKEY, RECOVERYKEY, DIRECTION, ROUTERDAY, ROUTERCKEY, ROUTERNUMBER)
            VALUES(TO_TIMESTAMP(TO_CHAR(:1), 'yyyy-mm-dd hh24:mi:ss.ff3'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)"""
    try:
        trgtcrsr.prepare(SQL)
    except cx_Oracle.DatabaseError, exception:
        print ('Failed to prepare cursor')
        print Exception(exception)
        exit (1)
    try:
        trgtcrsr.executemany(None, recordsToWrite)        
    except cx_Oracle.DatabaseError, exception:
        print ('Failed to insert rows')
        print Exception(exception)
        exit (1)

    trgtcnn.commit()
    trgtcnn.close()
    source_connection.close()

def main():
    recordstowrite = BuildBindList(RunQuery())
    write_to_table(recordstowrite) 

if __name__ == "__main__":
    main()

Start with the basics - have you checked that the data retrieved by the Python script has milliseconds data? Is the Python script storing the value as a string value or as a timestamp? — MT0
– MT0, Commented Jun 20, 2016 at 15:01
This has little to do with SQL Server or Oracle, although using a timestamp as a primary key like that is NOT a good idea. In any case, dates have no format, they are binary values. If you don't convert them to text and use parameterized queries, you can pass the value as-is from one database to the other. What library are you using? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 20, 2016 at 15:06
I am fully aware that it is not a good idea. But, I am a lowly co-op and they don't want to change it. the key does have other columns though. — user5999614
– user5999614, Commented Jun 20, 2016 at 15:09
@user5999614 post the code! Without it you are asking people to guess! If the source data has milliseconds and the target field accepts milliseconds, the problem is in the script — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 20, 2016 at 15:28
From this Oracle article Fractional seconds of a date/time value passed as a bind variable will be truncated unless the setinputsizes() method is used between prepare() and execute(). — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jun 20, 2016 at 16:18

Panagiotis Kanavos · Accepted Answer · 2016-06-20 16:22:59Z

4

This Oracle article warns that :

Fractional seconds of a date/time value passed as a bind variable will be truncated unless the setinputsizes() method is used between prepare() and execute()

The article's example shows that to save milliseconds you need to specify the input size for the tvalparameter:

ts = datetime.datetime.now()
cursor.prepare("INSERT INTO python_tstamps VALUES(:t_val)")

cursor.setinputsizes(t_val=cx_Oracle.TIMESTAMP)

cursor.execute(None, {'t_val':ts})
db.commit()

answered Jun 20, 2016 at 16:22

Panagiotis Kanavos

134k16 gold badges211 silver badges270 bronze badges

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

2 Comments

user5999614 Over a year ago

using set inputsizes() with positional arguments gave me syntax errors so i switched to named arguments. calling the timestamp d_val I have trgtcrsr.setinputsizes(d_val=cx_Oracle.TIMESTAMP) between the prepare and execute and now i am getting ORA-01830: date format picture ends before converting entire input string

user5999614 Over a year ago

This was in fact the issue. I had to mess with it a bunch to get it to work but using the setinputsizes() fixed it.

Boneist · Accepted Answer · 2016-06-20 15:02:43Z

2

It's possible that what you're experiencing is something to do with your nls format settings. However, I don't see an issue with using to_timestamp:

with sample_data as (select '2016-06-01 05:45:06.003' col1 from dual union all
                     select '2016-06-01 12:55:06.638' col1 from dual union all
                     select '2016-06-02 11:53:24.827' col1 from dual)
select col1,
       to_timestamp(col1, 'yyyy-mm-dd hh24:mi:ss.ff3') col1_ts,
       to_char(to_timestamp(col1, 'yyyy-mm-dd hh24:mi:ss.ff3'), 'yyyy-mm-dd hh24:mi:ss.ff3') col1_ts_str
from   sample_data;

COL1                    COL1_TS                                            COL1_TS_STR                  
----------------------- -------------------------------------------------- -----------------------------
2016-06-01 05:45:06.003 01/06/2016 05:45:06.003000000                      2016-06-01 05:45:06.003      
2016-06-01 12:55:06.638 01/06/2016 12:55:06.638000000                      2016-06-01 12:55:06.638      
2016-06-02 11:53:24.827 02/06/2016 11:53:24.827000000                      2016-06-02 11:53:24.827

(the col1_ts column is the timestamp as stored by oracle being returned as a string in the default format (which may be different on different clients, depending on whether they have messed around with their nls formats), and the col1_ts_str is the timestamp returned as a string in the specific format I requested).

You should check that the data has been entered correctly by doing a to_char on your timestamp column, e.g.

select to_char(your_timestamp_column, '<format>') ts_col
from   your_table;

answered Jun 20, 2016 at 15:02

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

3 Comments

user5999614 Over a year ago

it weird because if run the select with the to_timestamp(col1, 'yyyy-mm-dd hh24:mi:ss.ff3') in SQL Developer it give the correct results. when it is used in the insert from the python script it does not.

Boneist Over a year ago

So if you run the insert via SQL Developer, does it give you the same result as when you run it in Python?

user5999614 Over a year ago

Yea it works there too. Maybe it python. if i debug the python script and look at the records pulled from sql server it is saying that the type of the date values is datetime.datetime. But the milliseconds are still there. datetime.datetime(2016, 6, 1, 5, 45, 6, 20000)

rmcgarry · Accepted Answer · 2022-06-14 17:47:55Z

0

The Oracle article listed in some of the answers and comments is no longer available at Oracle's website. An archived copy of it can be found here:

https://web.archive.org/web/20180610041420/http://www.oracle.com/technetwork/articles/dsl/prez-python-timesanddates-093014.html

For the purposes of using executemany() on many rows of data, I discovered the bind values are positional, not named. You will need to run setinputsizes using the list/tuple method.

cursor.setinputsizes(None, None, None, cx_Oracle.TIMESTAMP, None, None, None)
cursor.executemany(sql, data)

answered Jun 14, 2022 at 17:47

rmcgarry

12 bronze badges

Collectives™ on Stack Overflow

sqlserver datetime to oracle timestamp problems

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest