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

[Dan Lewis <elderdanlewis@gmail.com>]

On Sun, 2012-02-19 at 14:38 +0200, Pertti Rönnberg wrote:
> 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

     DAY(date) does not exist as a built-in function when using LO. What
should work in your case is DAYOFMONTH(date). This returns the day of
the month (1-31) for the date entered.
     Another item, "Table".* tells the query to return all fields of the
table. You don't need to include this in the query. This is a summary
query and limits what fields can be used in this query. If you don't
know what this is, download Getting Started with Base chapter in the
Getting Started Guide from
http://www.libreoffice.org/get-help/documentation/.

     More likely what you need to use is:
SELECT DATEDIFF( 'mm', "BirthDate", CURRENT_DATE ) / 12
 - CASE WHEN MONTH("BirthDate")=MONTH(CURRENT_DATE) AND
 DAYOFMONTH("BirthDate")>DAYOFMONTH(CURRENT_DATE) THEN 1 ELSE 0 END AS
"Age" FROM "Table"

--Dan


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