nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Feature Layer - Date Query Async Exception..txt.zip

KirkKuykendall1_5-1639430895816.png

Find more posts tagged with

Accepted answers

SomeGISGuy

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

All comments

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);

}

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

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

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'

SomeGISGuy

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.