0

I'm uploading all the records in a data frame to a SQL Server table but to_sql seems to be rounding datetime values. ie:

assignee created_date        updated_date
my_name  2019-09-16 14:17:23 2019-11-26 14:48:39.261

Both dates are set as datetime64[ns]. Then, I call the following method:

df.to_sql(MY_TABLE_NAME, engine, if_exists='append', index=False, dtype=dtypes)

dtypes has the content bellow:

{'assignee': NVARCHAR(length=255), 'created_date': <class 'sqlalchemy.sql.sqltypes.DateTime'>, 'updated_date': <class 'sqlalchemy.sql.sqltypes.DateTime'>}

When I look at the updated data in SQL Server, I get the following updated_date:

2019-11-26 14:48:39.260

And the correct updated_date is (261 instead of 260):

2019-11-26 14:48:39.261

Any ideas why is this happening?

Thanks everyone!

briba
  • 2,857
  • 2
  • 31
  • 59

1 Answers1

0

The workaround here is to remap floats to doubles. Here are some links on some of this history of this issue. It appears that this is the result of differences with sqlalchemy and how it interacts with SQL flavors in regard to typing.

Link to related question on Stack Overflow

Link to Git Issue Thread on Pandas

Ryan Marinelli
  • 90
  • 10
  • Thanks for the help! :) But actually those are datetime fields. My solution was just to remove milliseconds from the dates, but I still wonder if that's the best way. Git issue apparently was closed in 2015. Thanks Ryan! :) I'll make a few tests more. – briba Jan 16 '20 at 16:09
  • There should also be some relation with this one: https://stackoverflow.com/questions/53208187/datetime-with-miliseconds-from-pandas-dataframe-to-mysql-db?noredirect=1&lq=1 – briba Jan 16 '20 at 16:12