Date columns and ODBC

My organization is currently migrating from MySql 4 to MySql 5.0.41.  Our
software uses the ODBC classes in MFC (CRecordset and CDatabase) to update
certain information in our DB.

Certain tables in our DB have columns of the DATE type.  When we update
these columns with ODBC (using the RFX_Date function), the actual query
generated ends up looking like:

UPDATE ... WHERE `DOB` = '19700505000000';

(I presume the WHERE clause is generated by the ODBC cursor library).  In
MySql 4 this would match rows where the DOB column was '1970-05-05.'
However, in MySql 5.0.41, this doesn't match anything.

I note that queries like:

UPDATE person SET `dob` = '19650609000000';

work correctly in MySql 5.0.41; they don't even result in a warning.  This
is very strange to me; it seems odd that you can set a field with UPDATE or
INSERT (without even a warning), yet a subsequent SELECT with the same
clause will not return any results.

Is this intentional behavior?  I don't see any way to fix this in our code
(short of changing all our DATE columns to DATETIME).  However, it doesn't
seem like an issue in MyODBC either.  The ALLOW_INVALID_DATES mode doesn't
seem to affect this behavior.  We are using Windows XP SP2 and MyODBC
3.51.15.

I would appreciate any help anyone can provide.

Craig Holmquist




-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@xxxxxxxxxxx