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: [email protected]
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
