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


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

Context


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.