Bug #75925 ODBC 5.3(a) Driver Invalid time(hours) format
Submitted: 17 Feb 2015 8:14 Modified: 14 Mar 2015 22:34
Reporter: Ameda NA Email Updates:
Status: Verified Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.3.4 OS:Windows (7 64bit)
Assigned to: CPU Architecture:Any
Tags: odbc invalid time format

[17 Feb 2015 8:14] Ameda NA
MySQL ODBC Driver 5.3 Tree (GA) 2014-07-18 returns errors on basic sql date/time queries.

How to repeat:
SELECT (TIMEDIFF(NOW(), table1.entered) as expression1;

>ERROR [22007] [MySQL][ODBC 5.3(a) Driver][mysqld-5.7.5-m15-log]Invalid time(hours) format. Use interval types instead

Interfaced via New System.Data.Odbc.OdbcConnection (ANSI)

Suggested fix:
ODBC 5.2.7 (GA) returns h:m:s

[25 Feb 2015 7:38] Chiranjeevi Battula
Hello Ameda NA,

Thank you for the bug report.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net), MySQL Connector/ODBC 5.3.4 but couldn't trace out any issue in time format.
Could you please list out the steps you tried out at your end? Please provide repeatable test case in order for us to confirm this issue at our end.

[25 Feb 2015 7:40] Chiranjeevi Battula
Result time format screenshot

Attachment: 75925.PNG (image/png, text), 5.11 KiB.

[25 Feb 2015 8:53] Ameda NA
replica database

Attachment: alpha.sql (application/octet-stream, text), 3.39 KiB.

[25 Feb 2015 8:53] Ameda NA
SELECT t0.id, jobno, street1, street2, t0.idpostcode1, t0.idpostcode2, idcargo, idbusiness0, idbusiness1, idbusiness2, t0.entered, iddriver1, scheduled, t0.notes, t0.price, TIMEDIFF(NOW(), t0.entered)  AS Ex1, scheduled, items, contact, claim, `order`, rego, phone, sms FROM `sale-trackers` AS t0
[25 Feb 2015 8:55] Ameda NA
New System.Data.Odbc.OdbcCommand > DRIVER={MySQL ODBC 5.3 ANSI Driver};
[25 Feb 2015 11:59] Chiranjeevi Battula
Hello Ameda NA,

Thank you for your feedback.
Verified this behavior on Visual Studio 2013 (C#.Net) and MySQL Connector/ODBC 5.3.4.

[14 Mar 2015 22:34] Ameda NA
Driver behavior needs addressed in [5.3] and published.
[3 Jul 2017 11:45] BenoƮt Decorte

Is this bug already solved? In which version of the driver? We are using the ODBC driver in version 5.3.4 and I also tried with the version 5.3.8 but the behavior is the same...

Thank you,

[13 Feb 2018 21:54] Edson Gomez
this problem is solved!! but you need to add (TIME_FORMAT)(,'%H:%i:%s') 

Below is the example this is in VBA

SQLStr = SQLStr & ", TIME_FORMAT(sec_to_time(sum(case when sub_status ='00:00:00' then pause_sec else 0 end)),'%H:%i:%s')