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

Hello Jerry,

I know how MS Excel handles dates etc. I am tring to keep data calculations
on mySQL side as I will benefit from this when I will do pivotable reports
in Excel - I will have an auto named data for my report.

I tried to modify my query using CAST, but this function is not working in
database. BTW, I don't know why. Knowing that, I used

CONVERSE (TIMEDIFF('2010-02-08 07:31:33', '2010-02-05 05:32:51'), binary)

Again, data in MS Query window is correct (only hh:mm:ss part), but after
returning to Excel column with timediff calculation is not present.

I also tested what if I convert result into CHAR type - Excel is displaying
my calculation column and data in this column is correct, but it looks like
Excel is fooled by ODBC to treat data as character strings. Just entering
any cell and confirming with ENTER key changes data into "real" time value
that can be used by Excel for calculations.

Regards

Adam

2010/10/8 Jerry Schwartz <[email protected]>

> >-----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
>
>
>
>