"date" vs "timestamp" use for SQL Query of date field in file geodatabase

JoeBryant1

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?

JoshuaBixby

For file geodatabases, the DATE, TIME, and TIMESTAMP SQL data types all map to the same Esri Date data type.   So, as far as file geodatabases are concerned, it doesn't matter which one you use when writing SQL statements. The reason the documentation focuses on using SQL DATE is because it shares the same name as Esri Date, so the shared name provides a logical transition working between the two.

In your case, you are not passing straight SQL to the file geodatabase, you are using another tool (Model Builder) to build an expression that is eventually passed to the file geodatabase.  The issue you are running into isn't with file geodatabases but either with Model Builder or possibly even Make Query tool.  The Make Query tool is somewhat infamous for how many issues it has with building proper syntax using the dialog tools.