(answer to myself & anyone interested ;0)
After setting my date to "Unix_Timestamp"
mode, I convert it to a 10-base number:
-----------------------------------------
>> CONV(UNIX_TIMESTAMP(U.lastTime),10,10)
-----------------------------------------
Then, I perform the substraction:
-----------------------------------------
Select
( CONV(UNIX_TIMESTAMP(U.lastTime),10,10)
- CONV(UNIX_TIMESTAMP(D.prevTime),10,10)
) as dateDiff
FROM ...
-----------------------------------------
which give the correct results as:
------------
dateDiff
------------
>> 1294572
>> 652223
>> -19864771
------------
This convertion isn't needed when I set up
the query in my mySQL-GUI but when executed
by VBs/ASP, an empty RecordSet is returned.
So firstly, I had to find the cause. After
some trial/error, the Unix_Timestamp seemed
to be the source of trouble. This convertion
is a solution that does the trick.
My guess is that the ODBC driver is
behaving badly in this case ...
Cheers,
bart
> Van: Bart Goormans
> Verzonden: zondag 16 december 2001 9:21
>
>
> There seems to be a flaw in the myODBC driver
> 02.50 when working with the Unix-Timestamp()
> function.
>
> Apparently, the driver doesn't know which
> data-type to use...
>
> When I send the sql statement:
> --------------------------------------
> Select
> ( UNIX_TIMESTAMP(U.lastTime) -
> UNIX_TIMESTAMP(D.prevTime)
> ) as dateDiff
> FROM ...
> --------------------------------------
>
> I get an empty recordset, but no Error-message.
>
>
>
> When I change to ...
> --------------------------------------
> Select
> concat(
> ( UNIX_TIMESTAMP(U.myTime) -
> UNIX_TIMESTAMP(D.latestDwnl)
> )
> , ' testDummy'
> ) as dateDiff
> FROM ...
> --------------------------------------
>
> I get the results as to be expected
>
> >> 1294572 testDummy
> >> 652223 testDummy
> >> -19864771 testDummy
>
>
> This workaround would be complete if I could convert
> this string to an integer.
> Or is there a beter way altogether, to calculate the
> difference between two dates?
> Not the same as the SUBDATE()-function, is it?
>
>
> best regards,
>
> bart>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
