Feature Layer - Date Query

[JonDicken]

Hi,

I was hoping you could clarify the correct syntax for performing a date query on an attribute in the runtime.

Our queries were working in 10.2.3, but now return the exception 'SQL error or missing database : ', though the only change we have made is to use the new runtime.

We are performing our queries using the QueryAsync method, with a SpatialQueryFilter which includes a geometry to query the layer with as well as a where clause containing ATTRIBUTE_NAME > date '2000-08-13 12:00:00' for example. I have attached the full stack trace.

Interestingly if I don't add the geometry to the query then I don't see the exception, but I also don't get the expected features returned.

[dotMorten_esri · Accepted solution]

Local queries are based on SQLite. See the SQLite query language doc: SQLite Query Language: Date And Time Functions

[TuukkaJärvinen]

I have the same problem. The following query works from SQLDeveloper

SELECT *

FROM TABLE

WHERE  DATEFIELD < date '2011-10-21'

The query also works when executed from the ArcGIS Server interface

/arcgis/rest/services/ServiceName/FeatureServer/4/query

and the where clause is set to "DATEFIELD < date '2011-10-21'"

It used to work same way with QueryAsync method in 10.2.3 but now it returns the 'SQL error or missing database'

[JonDicken]

As Morten notes, this will need to use the SQLite syntax now. Basically change your query to use the date (or datetime if needed) function as below:

SELECT *

FROM TABLE

WHERE  DATEFIELD < date('2011-10-21')

It would have been nice if this was included in the summary of changes document issued on release. But I hope this helps you!

[TuukkaJärvinen]

Does that return features from the service or just the local table? I'd like to query the service with the date field.

edit* I'm trying to query ServiceFeatureTable.

[JonDicken]

I believe using QueryAsync returns from both the service and the local table in 10.2.4, but you can pass in a parameter to only use local data if required. I found this to work when updating our code from 10.2.3.

The following is from the ServiceFeatureTable section of the ESRI Summary Of Changes PDF document:

For example, in 10.2.3 Beta a QueryAsync call would only return results from cached data. To perform an online query you had to use the service counterpart, QueryServiceAsync. In 10.2.4, QueryAsync calls will query both service data and cached data unless you specify the optional parameter forceLocal to be true, at which point only the local cache will be queried.

So this should do the trick for you, but I am sure someone from ESRI (or yourself) can confirm.

Cheers

[TuukkaJärvinen]

In 10.2.3 I had working date field queries with QueryServiceAsync, but now in 10.2.4 I updated it to use QueryAsync. All other queries work fine but where clauses to date fields return that 'SQL error or missing database'.

[TuukkaJärvinen]

My current workaround is to use QueryTask to get ObjectIDs because my date field where clauses still work  with the QueryTask.  Then I use QueryAsync with the ObjectIDs to get the actual features because I need to update feature attribute data with UpdateAsync.

[JenniferNery]

Thank you both for reporting this issue. ServiceFeatureTable where clause need to comply with the server back-end database as well.

This is a good workaround. Note, however that QueryTask result will not include any new features that had not been pushed to the server yet. To query cached results including local edits (i.e. new feature added, updated feature), you will need to use SQLite syntax with forceLocal parameter set to true.

Only for testing purposes, I set ServiceFeatureTable.Where to a clause that will yield no result and on query button click, I use QueryTask with query that service supports, QueryAsync(ids) to populate result onto my table and QueryAsync(queryFilter, true) with query that SQLite supports.

             xmlns:esri="http://schemas.esri.com/arcgis/runtime/2013">

    <Grid>

        <esri:MapView x:Name="MyMapView">

            <esri:Map InitialViewpoint="-117.22032735256052,34.03653059222634,-117.16707501525003,34.089782929536824,4326">

                <esri:ArcGISTiledMapServiceLayer ServiceUri="http://services.arcgisonline.com/ArcGIS/rest/services/World_Topo_Map/MapServer" />

                <esri:FeatureLayer ID="MyLayer">

                    <esri:ServiceFeatureTable ServiceUri="http://sampleserver6.arcgisonline.com/arcgis/rest/services/PhoneIncidents/MapServer/0"

                                              Where="1=2"

                                              OutFields="closeddate"/>

                </esri:FeatureLayer>

            </esri:Map>

        </esri:MapView>

        <Button VerticalAlignment="Top"

                HorizontalAlignment="Center"

                Content="Query"

                Click="Button_Click" />

    </Grid>

        private async void Button_Click(object sender, RoutedEventArgs e)

        {

            string message = null;

            try

            {

                var layer = MyMapView.Map.Layers["MyLayer"] as FeatureLayer;

                var table = layer.FeatureTable;

                ((ServiceFeatureTable)table).Where = null;

                var task = new QueryTask(new Uri(((ServiceFeatureTable)table).ServiceUri));

                var result = await task.ExecuteObjectIDsQueryAsync(new Query("closeddate > date '2012-12-01'"));

                if (result != null)

                {

                    var features = await table.QueryAsync(result.ObjectIDs);

                }

                var localFeatures = await ((ServiceFeatureTable)table).QueryAsync(new QueryFilter() { WhereClause = "datetime(closeddate, 'utc')  > datetime('2012-12-01')" }, true);

            }

            catch (Exception ex)

            {

                message = ex.Message;

            }

            if (!string.IsNullOrWhiteSpace(message))

                MessageBox.Show(message);

        }