Ask Your Question

Filter by year of date older than 70 years

asked 2017-09-20 21:01:07 +0100

bulldozer gravatar image

updated 2017-09-21 07:18:34 +0100

Hi, I'm relatively new to LibreOffice Base. I can't find information on how to use functions and calculations in sql. Id like to do something like the following

FROM tblPeople
WHERE YEAR(TODAY()) - YEAR(tblPeople.birth) > 70

Also I cant figure out how to exclude empty fields.

WHERE tbl.field not null

doent seem to work.

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted

answered 2017-09-20 22:07:15 +0100

Ratslinger gravatar image

updated 2017-09-21 15:49:25 +0100

FYI - This is a forum for LibreOffice which is in many ways different than OpenOffice. However Base is similar. Original question corrected.

SQL date calculations are different depending upon the DB and its' version. LibreOffice comes with HSQLDB embedded v1.8 - documentation here. Your SQL is not truly looking for 'older than 70 years' since it only considers the year in the selection. With that said, just based upon your statement this will work:

SELECT * FROM "tblPeople" WHERE (TO_CHAR( TODAY, 'YYYY' ) - YEAR("birth")) > 70

For the second part of the question, use IS NULL and IS NOT NULL for checking empty fields.

If this answers your question please click on the โœ” (upper left area of answer).

edit flag offensive delete link more


Thanks for the hint to the HSQLDB doc and the fixed query. Also the hint on the โœ”-button was helpful, wouldn't have found it without ๐Ÿ˜‰.

bulldozer gravatar imagebulldozer ( 2017-09-21 18:02:10 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-09-20 21:01:07 +0100

Seen: 66 times

Last updated: Sep 21 '17