I created a new database from scratch and copied the above query string
without modification.
This is the resulting file:
http://www.mediafire.com/file/lsx9jxlesvhw7vd/AgeCalculator.odb
--
View this message in context:
http://nabble.documentfoundation.org/Fw-LibreO-Base-is-lying-about-my-age-tp37
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"
I tested that query literally with a table named "Table" having a date
column named "BirthDate" in
Functions TO_CHAR, TO_DATE and TO_TIMESTAMP convert between strings and date
values and accept '' as part of the string format specification so the
function gets a hint that the text assumes dd/mm/ format, -mm-dd or
whatever. These functions are unavaillable in the built-in HSQLDB 1.8
Andreas,
Thanks again for your answer - nothing to argue about because the matter
is secondary.
I had already read the HSQLDB v2.2 guid that you refer to. And as far as
I can understand the '' ought to be as sufficient as 'year'. Here is
a copy that confirms it:
/Table 10.1. TO_CHAR, TO_D
Andreas Säger writes:
> The problem has nothing to do with LibreOffice. It is a built-in function of
> the HSQL database engine. I am sure that it is designed to work like this.
> '' is not a valid argument for the datediff function.
> This is the home page of that software: http://hsqldb.org
On Thu, 2012-02-16 at 13:10 -0800, Andreas Säger wrote:
> The problem has nothing to do with LibreOffice. It is a built-in function of
> the HSQL database engine. I am sure that it is designed to work like this.
> '' is not a valid argument for the datediff function.
> This is the home page of
The problem has nothing to do with LibreOffice. It is a built-in function of
the HSQL database engine. I am sure that it is designed to work like this.
'' is not a valid argument for the datediff function.
This is the home page of that software: http://hsqldb.org/
Version 2.2 is documented here
Andreas - thank you for answering.
You certainly noticed that in my mail was two (main) issues:
1. >>I asked for help with a database problem when LibreO-Base's
basic function (DATEDIFF(''...)) calculates a wrong result;
I hope that you realize how important and severe that matter is both
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"
--
View this message in context:
http://nabble.documentfoundation.org/Fw-LibreO-Base-is-lying-about-