Filter by year of date older than 70 years

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.

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 :heavy_check_mark: (upper left area of answer).

Thanks for the hint to the HSQLDB doc and the fixed query. Also the hint on the :heavy_check_mark:-button was helpful, wouldn’t have found it without :wink:.