Bug #28420 ODBC 3.51.15 driver doesn't translate MySQL 5.0.41 dates
Submitted: 14 May 2007 16:34 Modified: 23 May 2007 12:59
Reporter: Richard de Courtney Email Updates:
Status: Duplicate Impact on me:
Category:Connector / ODBC Severity:S1 (Critical)
Version:3.51 & 5.1 latest sources OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: dates, ODBC

[14 May 2007 16:34] Richard de Courtney
After upgrading from ODBC 3.51.12 to ODBC 3.51.15, it appears as though the ODBC driver isn't translating a certain format of date outputted by MySQL server (which on our server is formatted as YYYY-MM-DD).

How to repeat:
When running:

SELECT DATE_ADD('2007-01-01', INTERVAL -weekday('2007-01-01') DAY)) AS WeekStart, date(DATE_ADD('2007-01-01', INTERVAL +(7-weekday('2007-01-01')) DAY)) AS WeekEnd

The outputted dates are:
WeekStart: 2007-01-01
WeekEnd: 2007-01-08

Which works great.. however when retrieving the dates using ASP and ODBC 3.51.15, the dates are returned as ???? unless each date_add above is wrapped in DATE()

See: http://friend.ac/dev/dateODBCerror.asp
(the output has to be wrapped in cStr() to output the data, otherwise a VBscript runtime error "Type Mismatch" error is show)

This worked previously using 3.51.12
[21 May 2007 11:39] Jeff Holmstedt
Have same issue using ODBC 3.51.12.  Cannot interpret use of Date() function using MS Access.  Reverted back to 5.0.27 and works fine again.  Unfortunately, I found that going back to pervious version was a difficult.  I had to end up removing MYSQL, and reinstall version 5.0.27 then restoring the database.  I think 5.0.41 changed something within the ibData file where you could no longer use data with pervious version.
[23 May 2007 12:59] Tonci Grgin
Hi Richard and thanks for your report. This is actually a duplicate of Bug#10491 which is worked on and here's why:

C:\mysql507\bin>mysql -uroot -T test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.38-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT DATE_ADD('2007-01-01', INTERVAL -weekday('2007-01-01') DAY) AS Wee
kStart, date(DATE_ADD('2007-01-01', INTERVAL +(7-weekday('2007-01-01')) DAY)) AS
Field   1:  `WeekStart`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       STRING
Collation:  binary (63)
Length:     29
Max_length: 10
Decimals:   31
Flags:      BINARY

Field   2:  `WeekEnd`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
Collation:  binary (63)
Length:     10
Max_length: 10
Decimals:   31
Flags:      BINARY

| WeekStart  | WeekEnd    |
| 2007-01-01 | 2007-01-08 |
1 row in set (0.01 sec)


Notice that both fields have "Flags:      BINARY" set. As for ASP page, it actually received "0x2007-01-01" and interpreted it like ????. There is nothing much ODBC connector can do, within specs, with wrong metadata returned from server.

The problem arose in 3.51.14 as a side effect of perfectly correct patch.