(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

Reply via email to