Selecting dates with Query widget

[AndrewNiederhauser1]

Is there a way with WAB DE 2.6 - 2.8 to set Query widget options for "Today", "Yesterday", "Last Week", etc? When a user selects today, the widget would pull the current date and query based on that value vs. having the user choose the specific date(s). 

Thanks, 

Andrew

[AdrianWelsh]

Andrew,

That's a good question. I wonder if you could have a drop down list that has these options (today, yesterday, etc.) and the underlying code could spell out the actual query.

I found this stacked exchange page that lists a few options on how to get dates like this that might be helpful:

sql server - SQL statement to select all rows from previous day - Stack Overflow 

[AndrewNiederhauser1]

Adrian, 

It appears this is already available in the widget by setting the "in the last" operator. However, I'm getting a "Query failed!" response even thought my data includes records with dates in the last 24 - 48 hours. 

[RobertScheitlin__GISP]

Andrew,

  What is the underlying database or data source for the layer you are trying to query. Does that service have standardized queries feature enabled.

[AndrewNiederhauser1]

Robert, 

We're using SQL Server 2008 R2 with ArcGIS Server 10.3.1. Reviewing the link below and our system (screenshot below), it appears we have standardized queries enabled. 

About standardized queries—Documentation (10.3 and 10.3.1) | ArcGIS Enterprise 

Thanks, 

Andrew

[AndrewNiederhauser1]

and I feel silly now b/c I can easily see it's enabled via REST endpoint on the service used.  

Use Standardized Queries: true

[RobertScheitlin__GISP]

Andrew,

   At the rest service page can you try and query your layer using this SQL where clause?

Date BETWEEN CURRENT_TIMESTAMP -1 AND CURRENT_TIMESTAMP

Actually I am pretty surprised that you are using "Date" as a field name in SQL Server as this is considered an ODBC Reserved Keyword (this might me part of your issue).

[AndrewNiederhauser1]

Robert, 

I went to the layer's REST URL, clicked on Query operation and entered that SQL statement in the Where parameter then hit Query (Get). Result was Failed to execute query. Below was the URL after the failed query. Please note I changed our domain, AGS folder, and service name. 

Field name is Date.

• Date ( type: esriFieldTypeDate , alias: Date , length: 36 )

https://myserver/arcgis/rest/services/ServerFolder/ServiceName/MapServer/11/query?where=Date+BETWEEN... 

Thanks for your help, 

Andrew 

[RobertScheitlin__GISP]

Andrew,

  Then it might have to do with this:

Actually I am pretty surprised that you are using "Date" as a field name in SQL Server as this is considered an ODBC Reserved Keyword (this might me part of your issue).

[AndrewNiederhauser1]

Logged a case with Esri tech support. This issue has been addressed in WAB DE 2.7 and 2.8. Only workaround provided for WAB DE 2.6 was to convert the field to type text but that doesn't seem viable as the operators then change. The support analyst mentioned a bug that could be related. The bug has a different error syntax but analyst mentioned it's possible the bug was logged before getting to the particular error I was seeing. 

BUG-000092975, Using the Query widget on a date field in a service published to ArcGIS for Server from a SQL Server geodatabase results in the error, “Incorrect syntax near [date]”.

Thanks all for your input!

Andrew