topic Re: "date" vs "timestamp" use for SQL Query of date field in file geodatabase in Geodatabase Questions

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

JoeBryant1 — Fri, 20 Mar 2020 22:39:40 GMT

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?

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

JoshuaBixby — Sat, 21 Mar 2020 20:35:09 GMT

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.

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

JoeBryant1 — Tue, 24 Mar 2020 23:55:36 GMT

Thanks for clarifying, Joshua.

I did some tests and found that both "date" and "timestamp" run successfully in the Make Query Table tool. However, only the word "timestamp" auto-completes in blue in the SQL query dialog box; "date" does not. I'm not sure why, but it must have some indication of what's going on behind the scenes in the software.

Dragging the tool history into Model Builder also runs fine inside Model Builder, with no warnings, in Clause and SQL query modes.

However, when I save the model and open it as a GP Tool, I get an error saying "Internal Error initializing expression."

If I try to switch from SQL to Clause mode, I get a warning that "the source layer or table is not valid". This is with the input layer hard-coded into the tool, not set as a parameter.

This definitely backs up your assertion that this is a combination Model Builder / Make Query Table error, and has nothing to do with the data type keyword.

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

JoeBryant1 — Wed, 01 Apr 2020 15:35:16 GMT

FYI: This is now an official bug - #BUG-000129778 Make Query Table tool does not allow clause mode editing of SQL expression parameter.