RE: timediff results imported via ODBC ver. 3.51.27 - feature or issue?

>-----Original Message-----
>From: adas71 Gazeta.pl [mailto:[email protected]]
>Sent: Friday, October 08, 2010 6:30 AM
>To: [email protected]
>Subject: timediff results imported via ODBC ver. 3.51.27 - feature or issue?
>
>Hi list,
>
>I am trying to import into MS Excel 2003 results from timediff calculation.
>I am using MS Query to define query to retrieve my data and in MS Query
>window I get correct results - I mean time in hh:mm:ss format, but after
>switching to Excel column with results is changed. Excel adds current date
>to data queried from database. Example:
>
>in MS Query:
>
>2010-02-05 05:32:51 | 2010-02-08 07:31:33 | 73:58:42
>
>In Excel
>
>2010-02-05 05:32 2010-02-08 07:31 10-paz-11 01:58:42 I tried to look up is
>it a feature or a bug, but after hours I am still no sure how to treat this.
>What are your opinions on this problem?
>
[JS] It is a fundamental design in Excel. Excel stores dates as floating-point 
numbers. The only way to make them dates is by formatting them as dates. What 
is happening to you is that Excel is being told by ODBC that it is getting 
date/time data for that field.

Try using

CAST(TIMEDIFF('2010-02-08 07:31:33', '2010-02-05 05:32:51') AS BINARY

and see what you get.

If you need to do this on the Excel side, try formatting the cells with a 
custom format:

[hh]:mm:ss

If that doesn't work, you'll need to perform a calculation in a separate 
column:

=INT(A1*24) & ":" & INT(MINUTE(A1)) & ":" & SECOND(A1)

where A1 holds the results of your query. This should work regardless of your 
local date format, **I THINK**. I couldn't

>rgds Adam




-- 
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe:    http://lists.mysql.com/[email protected]