I had been trying to figure out why a SQL Query expression was giving me a syntax error using Model Builder in ArcGIS Pro. I had been constructing the query as instructed here: SQL reference for query expressions used in ArcGIS—ArcGIS Pro | Documentation, which says that you should use the word "date" before the string containing your date value in the expression. Example from that page:
INCIDENT_DATE >= date '2011-01-01 00:00:00'
However, this was giving me a syntax error when using the Make Query Table tool in ModelBuilder. (*Note: I was not able to edit the expression in Clause mode, which may be a bug related to the Make Query Tool described here). It wasn't until I created the expression in the GP Pane that I saw that Pro was constructing the query as:
Sampling_Date = timestamp '2020-02-10 00:00:00'
As mentioned, this is using data from a table in file geodatabase, from a field of type date.
When do I use "date", and when do I use "timestamp"? Is it the difference between a date field in a FGDB feature class and an FGDB table?