[egenix-users] mx.DateTime: Truncating fractional seconds

[M.-A. Lemburg]

[put the list back on CC since I believe this is of interest
  to others as well]

Steve Holden wrote:
>>I'm not sure what you mean. If it's the warnings that you get in
>>Python 2.3 from the time module functions if you pass in the
>>.tuple() value due to mxDateTime representing the seconds as
>>float, then this problem will be gone in 2.1.0 final (I decided
>>to make seconds in the .tuple() output an integer; the float
>>value is still available through the .seconds attribute).
>>
>>If you're talking about the str() representation which includes
>>fractions, then the easiest way to get rid off the fractions
>>is use .strftime() which doesn't handle fractions of a second
>>by nature :-)
> 
> I'm actually talking about using a DateTime object as a parameter for
> inserting into an Access Date column, specifically in this case under
> Python 2.2.x:

Ah, ok. AFAIR, Access tells mxODBC to send the date as string
and since mxDateTime instances generate seconds with fractions
which Access doesn't support, you get the warning.

This simple trick should work:

def AccessDateTime(dt):
     # cut away the seconds fraction part
     return str(dt)[:-3]
curs.execute(stmt, (AccessDateTime(now()),)

> $ python
> Python 2.2.3 (#1, Jun 19 2003, 12:10:13)
> [GCC 3.2 20020927 (prerelease)] on cygwin
> Type "help", "copyright", "credits" or "license" for more information.
> 
>>>>import mx.ODBC.Windows as db
>>>>conn = db.connect("billing")
>>>>curs = conn.cursor()
>>>>from mx.DateTime import now, DateTime
>>>>curs.execute("INSERT INTO Task (pjName, TaskName, Description, Rate,
> 
> Started)"
> ...              " VALUES('gpcc', 'TEST', 'Delete me after this!',
> 125.0, ?)",
> ...              (now(), ))
> Traceback (most recent call last):
>   File "<stdin>", line 3, in ?
> mx.ODBC.Windows.Warning: ('01S07', 13, '[Microsoft][ODBC Microsoft
> Access Driver]Fractional truncation (null)', 5696)
> 
>>>>n = now()
>>>>curs.execute("INSERT INTO Task (pjName, TaskName, Description, Rate,
> 
> Started)"
> ...              " VALUES('gpcc', 'TEST', 'Delete me after this!',
> 125.0, ?)",
> ...              (DateTime(n.year, n.month, n.day, n.hour, n.minute,
> int(n.second)), ))
> 1
> 
> Unfortunately I'd already tried Wade's int() trick on the whole date,
> without success in Access, I'm afraid, so it's interesting to know it
> works with MS SQL Server:
> 
> 
>>>>curs.execute("INSERT INTO Task (pjName, TaskName, Description, Rate,
> 
> Started)"
> ...              " VALUES('gpcc', 'TEST', 'Delete me after this!',
> 125.0, ?)",
> ...             (int(n), ))
> Traceback (most recent call last):
>   File "<stdin>", line 3, in ?
> mx.ODBC.Windows.DataError: ('22003', 34, '[Microsoft][ODBC Microsoft
> Access Driver]Numeric value out of range (null)', 5696)
> 
> Maybe the DateTime.__int__() method could be defined in some helpful way
> here?

int(datetime) returns the Unix ticks value for the datetime
you pass in. It is not meant to be used in this context.
The idea is that you can pass DateTime instances to other
functions which expect a Unix ticks integer.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Aug 09 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________