[libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
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 both parsed mode and in direct SQL mode. It selects all the table columns plus the calculated age. I used the embedded HSQLDB 1.8 in LibO 3.3.4 under Linux. -- View this message in context: http://nabble.documentfoundation.org/Fw-LibreO-Base-is-lying-about-my-age-tp3749948p3758292.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
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-tp3749948p3758312.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
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-my-age-tp3749948p3749995.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
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 for
LibreO and for every user: if a user can't trust the calculations then
better not to use LibreO-Base.
2.I pointed out that there must be a fault in the the function's
programmed construction when the function requires the string 'year' in
stead of '' the latter form is the modeled in 'official' guides.
They ought to be interchangeable.
Without answering or commenting the two points above, you sent to me a
SQL command that - as to my experience - is bullshit
it did not work in my database so I started to test it
I made a simple database backwards from your SQL command: one
table table with tree fields (id, birthdate, age), inserted one
record, let the query wizard make the query and copy-pasted (note!) your
SQL command into the criterion field in column 'age' -- it did not work
neither did it work as an expression in an 'extra' field(column)
your SQL command had syntax errors both in the command build and
the characters used (e.g. ,', [])
As you may understand from my message I had myself already tried to
build up a SQL command similar to yours - but I was not good enough in SQL.
Question:
Does LibreO recognize expressions? if so, what is the syntax?
And then a little psychology
In an recent other thread was a discussion about how to get more people
to use databases - 'how to lower the barriers'. One point of view seemed
to be that ordinary non-professional users feel it too difficult to
design dbs and then also to use them. What do you think if considering
my writings above?
I really want to think that you are an expert on databases - show it!
Please, try again and give me an adequate answer on my first problem.
Without wasting any more of your time.
Pertti Rönnberg
_PS: ref to Tom's and Dan's kind answers today_
Mathematics is an international language with it's commonly fixed rules.
And the only relevant answer is that the calculations in LibreO-Base
(and Calc!) really follow the math's rules!
As I mentioned in my mail: the result is not logical. An INT() function
reduces 72,225 to 72(,oo...) and there is no excuse in the world to
round 7,225 to 73!
I am serious! I do use both Calc and Base in matters where the correct
calculation is important.
I would not want to accept LibreO (nor OOo?) as a kindergarten play!
pr
On 16.2.2012 14:16, Andreas Säger wrote:
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-my-age-tp3749948p3749995.html
Sent from the Users mailing list archive at Nabble.com.
--
For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
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 that software: http://hsqldb.org/ Version 2.2 is documented here: http://hsqldb.org/doc/guide/ Version 1.8 is the integrated database engine of LibreOffice. Its documentation seems to be gone by now. -- Here is a link for version 1.8: http://www.odfauthors.org/openoffice.org/english/userguide3/db3/hsqldb-guide-pdf/view Uploaded a copy. --Dan -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
Andreas Säger ville...@t-online.de 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/ Version 2.2 is documented here: http://hsqldb.org/doc/guide/ Version 1.8 is the integrated database engine of LibreOffice. Its documentation seems to be gone by now. Actually, it's at http://www.hsqldb.org/doc/1.8/guide/ -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
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_DATE and TO_TIMESTAMP format elements 4-digit year * 4-digit year* YY 2 digit year/ Means that LO-Base shall definitely not stop working if '' is used -- which is also the normal format in most other calculating programs. And surely it has to do with LibreO(-Base) if it is a feature of HSQLDB that is embedded in LibreO -- I did load LibreO not HSQLDB. Go and take a look at your car. The car itself runs perfectly well without a steering wheel and the seats, but you as the user (driver) do need them; that is why they are installed (embedded) in the car. You did not focus on my main issue: /_how to get a correct calculation result._/ Now you are wasting not only your time but also mine. I remain waiting Best regards Pertti Rönnberg On 16.2.2012 23:10, 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 that software: http://hsqldb.org/ Version 2.2 is documented here: http://hsqldb.org/doc/guide/ Version 1.8 is the integrated database engine of LibreOffice. Its documentation seems to be gone by now. -- View this message in context: http://nabble.documentfoundation.org/Fw-LibreO-Base-is-lying-about-my-age-tp3749948p3751931.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Fw: LibreO-Base is lying about my age!
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 This is the documentation of the DATEDIFF function: DATEDIFF DATEDIFF ( field, datetime value expr 1, datetime value expr 2 ) field ::= 'yy' | 'mm' | 'dd' | 'hh' | 'mi' | 'ss' | 'ms' The function always rounds up to full integers stating that you are in your 73th year. Knowing this, you can calculate the common age (completed years) with my little formula. It gets the completed years from months divided by 12 and if the months are the same it tests if the today is before or after the birthday. -- View this message in context: http://nabble.documentfoundation.org/Fw-LibreO-Base-is-lying-about-my-age-tp3749948p3752245.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
