query string with quotes fails on integer fields

370
2
03-01-2022 02:11 PM
ChrisWiebke
New Contributor III

At 10.8 I was using a where clause with quotes around values that queried integer columns.  I also used a where clause for example, Date = '2024-01-01'.   These queries worked fine.  Since we have moved out testing environment to 10.9, the where clause no longer works with quotes if querying an integer field, and I had to add DATE before the date for the where clause to work.  I can't find any documentation about changes that caused the malfunction.  Does anyone have any insights into the where clause changes for the 10.9 arcgis rest api?  Thanks.

0 Kudos
2 Replies
KimGarbade
Occasional Contributor III

Usually these differences are caused by the requirements of the underlying database the query is being run against, but if that is the same in both the development and the production environments, then it might be because the 10.9 REST API uses the SQL-92 standard (see second image below.  It doesn't talk specifically about the REST API, but it mentions the 'date' keywork being required by SQL-92).  

https://desktop.arcgis.com/en/arcmap/latest/map/working-with-layers/sql-reference-for-query-expressi...

KimGarbade_0-1646183335429.png

file geodatabase use the "date" syntax

KimGarbade_1-1646183421373.png

With regard to the integer queries... never heard of one working with quotes unless its stored as text in the database.

 

jcarlson
MVP Notable Contributor

To add another to @KimGarbade 's excellent post, we also noticed from 10.8.1 to 10.9 that our queries required the format "timestamp 'yyyy-mm-dd'", where before, the word "timestamp" was not necessary. I suspect that the underlying DB in the portal changes between versions.

- Josh Carlson
Kendall County GIS