Date: prev next · Thread: first prev next last
2012 Archives by date, by thread · List index

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.


For unsubscribe instructions e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.