where <datefield> in (%s, %s)

[Sibylle Koczian]

A select or update query with such a where clause is executed correctly if and only if the parameters are given as strings of the format 'yyyy-mm-dd'. mx.DateTime objects or strings in other date formats ('dd.mm.yyyy') won't work: the select query finds the empty set and the update query doesn't update anything.

The similar clause "where <datefield> = %s" works with a mx.DateTime object as parameter.

Bug or feature?

Windows XP, MySQLdb 1.0.0, Python 2.3.4, MySQL 4.0.20. I think it's the same thing with Linux, but I didn't try the 'yyyy-mm-dd' strings there and I haven't got all version info at the moment.

[Sibylle Koczian]

In the meantime I repeated the queries on Linux (SuSE 9.1): same results with earlier Versions of everything:
MySQLdb 0.9.3b2, Python 2.3.3, MySQL 4.0.18 (everything out of the box with the Linux distribution).
Moreover there is no difference between mx.DateTime objects, datetime.date objects and the DateTime objects (whatever they are) MySQLdb returns from a query. They can be used as parameters for a clause "where <datefield> = %s", they cannot be used for a clause "where <datefield> in (%s, %s)". Why?

[Andy Dustman]

Well, it's because DateTime is a Date with Time, and you are trying to compare it to a Date. So use a Date object instead.

Also, are you sure "where <datefield> in (%s, %s)" does what you think it does?