Re: [libreoffice-users] LibreO-Base is lying about my age -- The End!

On Fri, 2012-02-17 at 16:49 +0200, Pertti Rönnberg wrote:
> Andreas, Dan, Drew - and others
> Thank you very much for your help and giving me your time.
> I really admire you for your enthusiasm to helping others.
>
> Even if I did not directly get all the answers, you forced me to 
> investigate - I have learned a lot!
> The situation regarding the function DATEDIFF('year', ...) in 
> LibreO-Base/HSQLDB seems to be:
>
> 1. >>    from SQL's point of view does LO-B&HSQLDB calculate the 
> function quite correctly.
>   It is not a matter of mathematics - nor are there any int() or 
> roundings;  it is a matter of (not so logical!) definition that you 
> simply have to be aware of.
> DATEDIFF() does not calculate the difference between the given dates:  
> it calculates the difference between the datepart values that it has 
> extracted from the given datevalues using the selected datepart type 
> (year, yy, mm, dd, etc).  From two dates 2.11.1939 and 17.2.2012 
> DIFFDATE('year',..) calculates with the 'year' values 1939 and 2012.
> With datepart 'mm' it calculates first from each given datevalue the 
> total amount of months, extracts them, and then calcs the difference; to 
> get the result in years the function shall be divided by 12 (as Drew 
> said).  Alternatively do DATEDIFF('dd',...)/365.
> 2.>>    in most other SQL db-generators 'yyyy' is a valid abbreviation 
> for 'year' but obviously not in HSQLDB (v.2.2) and thus not in 
> LibreO-Base either..

OK - just a quick note..

Base DOES NOT use hsql2.2, rather it's using 1.8 (and a patched versio n
of that to boot) .. so watch you don't get caught with inconsistencies
there from the 2.2 docs.

Also - / 12 is fine, but Andreas gave you the complete solution,
accounting for the current date being also your birth anniversary.

Anyway - good luck with your endeavors.

//drew



-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted