Query a feature layer with a parameter of type Date

[MaximilianGlas]

Hi,

I created a special Feature Collection with a table which contains a Date field.

//Create a feature collection and store it in a feature collection layer
var fields = new List<Field> {new Field(FieldType.Date, "Date", "Date", 20)};
var table = new FeatureCollectionTable(fields, GeometryType.Point, SpatialReferences.Wgs84);
table.Renderer = new SimpleRenderer(new SimpleMarkerSymbol{Color = Color.Blue, Size = 5});
var collection = new FeatureCollection(new [] {table});
var collectionLayer = new FeatureCollectionLayer(collection){Id = "FeatureCollectionLayer" };

var feature = table.CreateFeature();
feature.Geometry = new MapPoint(11.6,48.1, SpatialReferences.Wgs84);
feature.Attributes["Date"] = DefaultDate;
await table.AddFeatureAsync(feature);

Map.OperationalLayers.Add(collectionLayer);‍‍‍‍‍‍‍‍‍‍‍‍‍

Now: When I want to Query this feature layer, I am not able to get a result match for my search date:

//Now load try to query on this layer with a where clause on the date field
var layer = ((FeatureCollectionLayer)Map.OperationalLayers["FeatureCollectionLayer"]).Layers.First();
var result = await layer.FeatureTable.QueryFeaturesAsync(new QueryParameters()
{
    WhereClause = $"Date = '{DefaultDate}'"
});
layer.SelectFeatures(result);‍‍‍‍‍‍‍

The result is always empty, no selection was made.

DefaultDate by the way is a static value:

public DateTime DefaultDate { get; set; } = new DateTime(2019, 05, 08, 6, 0, 0);‍

In the end I used int32 instead of Date as the data type for the field and converted the date into an integer, so it finally worked. 

So my questions are:

1. Why does it not work with data type Date? What was wrong here?
2. Why does data type Date needs a length when creating the field? What is it for? Is it used?
3. I missed a data type boolean. Any reasons why it is missing?

Kind regards

Max

[MaximilianGlas · Accepted solution]

Ok, we got it:

var result = await layer.FeatureTable.QueryFeaturesAsync(new QueryParameters()
{
    WhereClause = $"datetime(Date, 'utc') = datetime('{DefaultDate.ToString("s", new CultureInfo("en-us"))}', 'utc')"
});‍‍‍‍

A bit tricky that doubly conversion of both sides regarding the correct date format and UTC time. But this way it worked for us.