AnsweredAssumed Answered

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

Question asked by jbryant_wya on Mar 20, 2020
Latest reply on Apr 1, 2020 by jbryant_wya

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?

Outcomes