query string with quotes fails on integer fields

Question

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.

jcarlson · Answer

To add another to @KimberlyGarbade '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.

KimberlyGarbade · Answer

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-expressions-used-in-arcgis.htm#GUID-85C03D85-F4A5-48FC-8E8C-3F79919430DB

file geodatabase use the "date" syntax

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