[ceodbc-users] Problem with Sql Server, DateTime, and milliseconds

[Frank M. <fr...@ch...>]

Hi all

I am using MS Sql Server 2005, python 3.2.2, and ceODBC 2.0.1.

My problem is with Sql Server, not with ceODBC, but I thought I would ask
here in case someone has figured out a workaround.

Python's datetime.datetime object uses a precision of microseconds. Sql
Server's DATETIME type only uses a precision of  milliseconds (to the
nearest 3.33ms).

When I try to insert a datetime.datetime object into a DATETIME column, the
microsecond portion is rejected, but the rest succeeds, so the object is
stored to the nearest second.

One possible workaround is, instead of inserting the datetime object
directly, use the following -

    dtm = dtm.isoformat(sep=' ')[:23]

This converts it to a string, and strips off the last three digits of the
microseconds, turning them into milliseconds. It works, but it is not
elegant, and it is quite disruptive to my existing code.

Does anyone have any better suggestions?

Thanks

Frank Millman

P.S. I believe that Sql Server 2008 has a new datetime type that stores
microseconds, but I would prefer to maintain compatibility with 2005.