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

Dennis, Jorge and Drew - thanks!
The functions rounddown() and yearfrac() are both new to me; I shall 
study and try them.

Drew,
I think that I have got my problem solved - I'm satisfied.
Not necessary, but of pure interest I should like to know what is wrong 
either in the writing of Andreas' solution or elsewhere - when 
copy-pasted into a db (LO-Base v.3.4.5) a pop-up window says first 
"column 'Select ..(etc)' is unknown" and then "syntax error".
The Andreas' version was (directly copy-pasted here):

SELECT "Table".*,
DATEDIFF( 'mm', "BirthDate", CURRENT_DATE ) / 12
- CASE WHEN MONTH("BirthDate")=MONTH(CURRENT_DATE) AND
DAY("BirthDate")>DAY(CURRENT_DATE) THEN 1 ELSE 0 END AS "Age"
FROM "Table"

Regards
Pertti Rönnberg


On 18.2.2012 3:21, Dennis E. Hamilton wrote:
> You should display =Now() to see what date format is being understood.  If it is dd/mm/yyyy, you 
> haven't had your 73rd birthday yet, according to LO.
>
> Also, dividing by 365 for such a long span is suspicious.
>
> Assuming that you want actual calendar time and the same formulas used by Calc work, try something 
> like (using my birthday):
>
>    A3 =ROUNDDOWN(YEARFRAC(DATE(1939;1;25);NOW();1))
>
> where DATE( ) is used to avoid formatting confusion and the YEARFRAC( ) actual-days basis is used 
> to compute the fractional years from the first date to the second.
>
> This got my age (73) correctly in LibreOffice 3.3.2 Calc.
>
>   - Dennis
>
> -----Original Message-----
> From: jorge [mailto:jrodriguez17@cpcecr.com]
> Sent: Friday, February 17, 2012 16:38
> To: users@global.libreoffice.org
> Cc: ptjr@elisanet.fi
> Subject: Re: [libreoffice-users] Fw: LibreO-Base is lying about my age!
>
> Hi:
>
>         I suggest to do this:
>
>         If
>
>         A1: 2/11/1939
>         B1: =Now()
>         
>         Input in A3:
>
>         =ROUNDDOWN ((B1-A1)/365)
>
>         outcome: 72
>
> Regards,
>
> Jorge Rodríguez
> _______________
>
>
>
> El jue, 16-02-2012 a las 13:40 +0200, ptjr@elisanet.fi escribió:
> > --- Alkuperäinen viesti ---
> > Aihe: LibreO-Base is lying about my age!
> > Päiväys: 15.2.2012 17:46
> > Lähettäjä: Pertti Rönnberg [ptjr@elisanet.fi]
> > Kopio: Pertti Rönnberg [ptjr@elisanet.fi]
> >
> > Dear all of you,
> > First an opinion - correct me if wrong!
> > I've got the idea that this is kind of a help-desk where every LibreO
> > user can both ask for help and give info about problems related to
> > LibreO's components. Without any bad feelings afterwards!
> > Referring to the irrelevant "debates" recently and also my own
> > experiences some weeks ago I would say that a real expert has the
> > ability to concentrate on the question as-it-is and give a relevant
> > answer -- he has knowledge enough to understand that there are no silly
> > or wrong questions.
> > Every question has its reason - and every question is valuable because
> > it indicates that there may be something to repair or do better in LibreO.
> > There is a truth in quality systems (e.g. ISO 9000): "without
> > identifying the weaknesses there is no development".
> >
> > _And now to my contribution to make LibreO better:_
> > I need to calculate in LO-Base the difference between an 'olddate' and
> > today expressed in whole years (no decimals)
> > The main procedure seems to be the same in LO-Base and OOo as in MSAccess.
> > The function I placed in the query field is (ref. also to guides in LO&
> > OOo):
> >       datediff('year',"table1"."olddate",current_date)  or
> > datediff('year',"olddate",current_date)
> >
> > When I inserted my birthdate (2.11.1939) in 'olddate' my computer states
> > that I am 73 - I'm NOT, I'm still only 72!
> > The correct result is 72,225 years;  and the desired answer without
> > decimals 72.
> >   >     I got the same wrong result (73) both in LO-Base and in OOo-Base
> >   >     the wrong result is not logical - it can not be a rounding up, nor
> > is it a result of an 'int()'-function
> >   >     if I replaced 'year' with 'dd' or 'mm' or 'yy' the function works
> > -- but
> > when replaced 'year' with 'yyyy' the function stopped working  (why -
> > 'year' and 'yyyy' ought to be equal!)
> >   >     the query field does not accept the expression "int(datediff(..etc))"
> >   >     the date-format is equal (finnish - see above) in every relevant field
> >   >     my OS is Windows7Professional/32Bit
> >
> > I also tried the formula /year(current_date - "olddate")/ but the query
> > did not accept it as a valid expression - can I put that formula
> > anywhere else?
> >
> > Anyone there who can tell me how I shall get the LO-Base to be honest?
> > Regards
> > Pertti Rönnberg


--
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