I'm using ArcGIS Pro 2.8.
Using Modelbuilder, I am attempting to select by attribute. I validate the expression and it returns that it is valid. However, when I attempt to run the model it returns ERROR 000358: Invalid Expression. The input is from an ArcGIS Server connection. Not sure what I'm missing here. I've attached a screenshot.
This sounds like a problem with your expression, not with ModelBuilder. SQL can vary between different ArcGIS data sources and especially different SQL databases, the expressions that work with some databases are not supported with others. I think you need to look at some PostgreSQL documentation and check the syntax for this type of query.
The "date" keyword may not be needed here, see:
SELECT name,end_date as left_date FROM employee WHERE end_date BETWEEN '1998-01-07' AND '2016-08-01';