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