3

I am trying to query the San Jose Crash Locations ArcGIS site by a esriFieldTypeDate field called CRASHDATETIME using the Python requests library. The where query was:

CRASHDATETIME < '1/2/2021'

I have used a similar query on esriFieldTypeDates for other ArcGIS sites with no issues. In this case, I got an error saying it was an unable to complete the operation.

To further test the issue, I went to the MapServer Query site to test out the query there and got the same result.

Here is an example of a similar query being completed successfully on another ArcGIS site (Fairfax County Police Arrests)

How should the query be properly formatted and why does the original query not work while this type of query has no issue on other ArcGIS sites?

UPDATE: I have now also tried the following based on Vince's suggestion. It still does not work. CRASHDATETIME < '2021-01-02'

4
  • 1
    Welcome to GIS SE. As a new user, please take the Tour. The ISO 8601 standard format for dates is ''YYYY-MM-DD" (obXKCD). ArcGIS may also support "MM/DD/YYYY", but you need the leading zeros on month and day. While SQL supports date and datetime datatypes, they are non-standard across implementations, and highly dependent on the underlying support class.
    – Vince
    Commented Feb 2, 2023 at 14:01
  • That's good info on date formatting. However, it still does not work when I format correctly. I'm about to update the question with links that I forgot and to note this.
    – Matt S
    Commented Feb 2, 2023 at 15:10
  • 1
    The most portable format for searching dates is: CRASHDATETIME < DATE '1/2/2021'
    – bixb0012
    Commented Feb 2, 2023 at 15:52
  • Thank you @bixb0012. This fixes the issue.
    – Matt S
    Commented Feb 2, 2023 at 16:05

1 Answer 1

2

To follow up on my comment from earlier, Esri's documentation for SQL queries through the ArcGIS REST APIs has some gaps and inconsistencies. For example, looking at the where documentation for Query (Feature Service/Layer) - ArcGIS REST APIs | ArcGIS Developers:

A WHERE clause for the query filter.

SQL-92 WHERE clause syntax on the fields in the layer is supported for most data sources. Some data sources have restrictions on what is supported.

So most, but not all, data sources support SQL-92 WHERE clause syntax, and some of those data sources have restrictions. Although the documentation does go on to give an example of a data source with restrictions, it is just that, an example. There is no explanation of what specific data sources do and don't support SQL-92 WHERE clauses, nor is there an explanation of what specific restrictions apply to what data sources. Worse yet, there aren't any references or links people can follow to get the gritty details.

I don't consider blog posts authoritative product documentation, even if the blog post is from the vendor, but sometimes the best information one can find is in a blog post. For SQL queries in the REST APIs involving dates and times, some of the better information is in a blog post: Querying Feature Services: Date-Time Queries - ArcGIS Blogs.

Date Functions

You can query esriFieldTypeDate (date-time) fields in two different ways: by DATE or TIMESTAMP date functions. Each type of date-time query must include a date function to make sure the query is treated in the proper way.

<DateField> = DATE 'YYYY-MM-DD'

<DateField> = TIMESTAMP 'YYYY-MM-DD HH:MI:SS'

When should you use each type of date-time query? Well that’s a good question, and the answer is that it depends on your data and what you want from it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.