title: sqlserver datetime to oracle timestamp problems
body: <p>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. </p>
  
  <p>The problem is that the dates look like this <code>2016-06-01 05:45:06.003</code> but if I drop the fractional seconds It will violate the primary key as there are plenty of records at <code>2016-06-01 05:45:06</code> but only one at <code>2016-06-01 05:45:06.003</code> so it must have milliseconds in it. </p>
  
  <p>I should mention that data type for the column in oracle is TIMESTAMP </p>
  
  <p>If I just pull the records and run my insert Which is just a basic insert with the values like this <code>VALUES(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15)</code></p>
  
  <p>Doing that gets me dates that look like this <code>01-JUN-16 07.05.41.000000000 AM</code>. 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. </p>
  
  <p>I figured the fix would be to be explicit about the format so I modified the insert statement values clause to be like this <code>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)</code>. This is even worse as it changes all the dates in every row to <code>16-JUN-20 12.00.00.000000000 AM</code> I also tried <code>YYYY-MM-DD HH24:MI:SS,FF9</code> but this yields the same results. </p>
  
  <p>I am sure this something stupid that I am missing but for the life of me I don't know what it is. </p>
  
  <p>Since asked, The data type of the column on the sql server table is <code>DateTime</code> and on the oracle server i have it currently set to <code>TimeStamp(3)</code> but I can change the data type on the oracle side.</p>
  
  <p>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. </p>
  
  <p>The python:</p>
  
  <pre><code>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()
  </code></pre>