Using year from a date field as a criterium

[hermanv]

LO database type firebird, has a table where te field “Ontslag” is date type.
The following select works OK.

SELECT "Ontslag" "Ontslag", "Naam" "Naam", "Voornaam" "Voornaam", CAST( EXTRACT( YEAR FROM "Ontslag" ) AS INTEGER ) as "jaar" FROM "Ledenarchief" ORDER BY "Naam" ASC

But I want now to select all from before 2022, so I write

SELECT "Ontslag" "Ontslag", "Naam" "Naam", "Voornaam" "Voornaam", CAST( EXTRACT( YEAR FROM "Ontslag" ) AS INTEGER ) as "jaar" FROM "Ledenarchief" ORDER BY "Naam" ASC where "jaar" < 2022

But that throws me syntax fault.
If I switch to Design view i.s.o. SQL view, I can write “< 2022” in the criterium under the cast-extract statement and the gives (translated from my running in Dutch): cannot compare the field with an integer number.
The firebird docs state that the extract should result in a number, but that on its own did not work either, so I tried the cast, but still in vain.

[Villeroy]

Try to keep it simple. The following works with Firebird3.

SELECT *
FROM "Ledenarchief"
WHERE EXTRACT(YEAR FROM "Ontslag")< 2022

[hermanv]

What I’ve done wrong, beats me. But starting from your code I get what I was aiming for.