| Bug #4437 | Formating 'datetime' field result in '?????' using ADO | ||
|---|---|---|---|
| Submitted: | 7 Jul 2004 18:41 | Modified: | 28 Jul 2004 22:36 |
| Reporter: | Sebastien Labrie | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S1 (Critical) |
| Version: | 3.51 | OS: | Windows (XP) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[7 Jul 2004 22:08]
Sebastien Labrie
One last thing... Here's how my tableA (example) look like: create table tableA (ID mediumint NOT NULL, dateFrom datetime NOT NULL, INDEX(dateFrom));
[20 Jul 2004 17:43]
Paolo Saudin
Hi, I've got the same problem on Win2K SP4, MySql 4.0.15, MyODBC 3.51.08 and ADO 2.8. When I retrieve data from a table with a datetime column "SELECT master.Fulldate ..." (Fulldate is the datetime column), I normally get a recordset whose first column is of adDBTimeStamp type (ado 135). As soon as I need aggregate functions as "SELECT SUBSTRING(master.Fulldate,1,13) as Fulldate ... GROUP BY Fulldate" to get hourly averages, the recordset field type change to adBinary (128) and is no longer treated as a valid date - the rs.field(0).value is ??????. Regards, Paolo
[27 Jul 2004 20:56]
Ian Neubert
I'm having the same problems, Windows 2000 Adv Server, ADO 2.8, MyODBC 3.51.08, IIS 5.0 w/ ASP
[28 Jul 2004 22:36]
Timothy Smith
Hi! This should have been fixed along with bug #4578; when 3.51.9 is released (should be this week), can you please test to ensure that it has solved your problem. Thank you! Timothy
[6 Apr 2007 2:53]
t yamo
I seem this problem has relapsed in 3.51.14. 3.51.12 works correctly (I don't have 3.51.13).
Description: Hi, I'm using VB6 to connect to MySQL using ADODB and MyODBC. The problem is... every time I want to format my datetime field using DATE() ,TIME() or even DATE_FORMAT() in my query, my field become bad values ('?????'). If I try my queries directly in MySQL, everything work fine! But using MyODBC and ADODB... I'm getting bad headaches! I think 'mySql ODBC 3.51 Driver' (or ADODB) have a bug when changing a datetime field. Perhaps mySql return a string (date formatted) but tell myODBC it's a datetime field??? I thought I was the only one with this problem but I just realize there's someone else... Take a look at: http://bugs.mysql.com/bug.php?id=4418 How to repeat: I am using: - Windows XP - VB6 or ASP.NET - ADODB 2.7 - mySql ODBC 3.51 Driver (version 3.51.08) - mySql 2.1.3 Example1: ---------------- select time(‘2004-06-11 17:35:00') test In mySQL I get: 17:35:00 In VB6 I get: 6/30/2004 (today's date)????? Example2: ---------------- SELECT DISTINCT DATE_FORMAT(dFrom,'%T') test FROM tableA WHERE dFrom >= '2004-06-11 16:00:00' In mySQL I get: test ---------- 16:00:00 16:30:00 17:00:00 ... In VB6: ????? ????? ????? ... Example3 (in VB6): ---------------- oConn.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=127.0.0.1;DATABASE=sportDB;UID=root;PWD=" oRec.CursorLocation = adUseClient oRec.Open "SELECT DISTINCT DATE(dateFrom) test FROM tableA WHERE dateFrom >= '2004-06-11 16:00:00' AND dateFrom <= '2004-06-13 18:00:00'", oConn Set oRec.ActiveConnection = Nothing oConn.Close Do While Not oRec.EOF print oRec!test oRec.MoveNext Loop In mySql, the query work fine returning: test ---------- 2004-06-11 2004-06-12 2004-06-13 In VB6, its all bad values '?????': ????? ????? ????? But, if I remove 'DISTINCT' from the query, in VB6 I get: 2004-06-11 2004-06-11 2004-06-11 2004-06-12 ... Suggested fix: I re-installed and tried many versions: ADODB 2.5 to 2.7 'mySql ODBC 3.51 Driver' version 3.51.05 to 3.51.08. mySQL 2.1.2 and 2.1.3 'OPTION=18475' in my connectionString... NOTHING CHANGE!