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.
The expression may be valid but not for that particular data source. Have you tried running the tool outside ModelBuilder?
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:
https://sqlserverguides.com/postgresql-date-between-two-dates/
which uses:
SELECT name,end_date as left_date FROM employee
WHERE end_date BETWEEN '1998-01-07' AND '2016-08-01';