Query FeatureLayer by date

[JimWharton]

I have a featurelayer with a field with "type: esriFieldTypeDate". I have a form on a page using the standard HTML5 date input type. (it uses the browser's built in datepicker) The format that this returns is in this format: '2014-04-29'. When I try to send this in a query, I get an error: "Unable to perform query. Please check your parameters."

What type of date format should I send in a query/querytask to be able to select dates before/after a chosen date?

I've tried using a javascript new Date(). I've tried sending it as 'YYYY-MM-DD', but nothing else.

[JakeSkinner]

Hi Jim,

Try the following:

var date = "2014-04-29"; 
date = new Date(date); 
date = (date.getMonth()+1) + "/" + (date.getDate()+1) + "/" + date.getFullYear(); 

//returns '4/29/2014'

query.where = "EnteredDate = date '" + date + "'"; 

[JimWharton]

Thanks!

Ok, so if slashed dates are the required format, I could do this:

var date = '2014-04-29'.split('-');
var esriDate = parseInt(date[1], 10) + '/' + parseInt(date[2], 10) + '/' + date[0];

[JimWharton]

Ok, sadly, that still is giving me an error. My query params are as follows:

f:json
where:time > '2/4/2014'
returnGeometry:true
spatialRel:esriSpatialRelIntersects
outFields:*
outSR:102100

If I omit any dates from my search and search on things like 'username'. (" username LIKE '%"+username+"%'") My searches work just fine. It's only the dates that fail.

[JakeSkinner]

Try the following:

where:time > date '2/4/2014'

[JimWharton]

Interesting, how do I get that when writing a query?

Currently, I'm setting it up like this:

        esriDate: function(date) {
          var dateArr = date.split('-');
          dateArr.push(dateArr.shift());
          return dateArr.map(function(el) { return parseInt(el, 10); } ).join('/');
        },
    
        buildQuery: function() {
          var query = [];
          var name = this.username.value;
          var startDate = this.startDate.value;
          var endDate = this.endDate.value;

          if(name) {
            query.push("username LIKE '%"+name+"%'");
          }

          if(startDate) {
            query.push("time > '"+this.esriDate(startDate)+"'");
          }

          if(endDate) {
            query.push("time < '"+this.esriDate(endDate)+"'");
          }

          if(query.length > 1) {
            return query.join(' AND ');
          } else {
            return query.join('');
          }
        },

        submitQuery: function(e) {
          e.preventDefault();
          var query = new Query();
          query.returnGeometry = true;
          query.where = this.buildQuery(); 
          this.layer.selectFeatures(query, this.layer.SELECTION_NEW, function(data) {
            console.log(data);
            // var extent = graphicsUtils.graphicsExtent(data);
            // map.setExtent(extent, true);
          });
          menus.closeFeatureQuery();
          this.destroy();
        }

The query looks "mostly" SQLish. How do I specify that it's a date?

[JakeSkinner]

Try this:

if(startDate) {
    query.push("time > date'"+this.esriDate(startDate)+"'");
  }

  if(endDate) {
    query.push("time < date'"+this.esriDat(endDate)+"'");
  }

[JimWharton]

Ok, so we're casting it as some sort of date type? Can I cast dates in the original format? (YYYY-MM-DD) or do they need to be in the mm/dd/yyyy format before they're casted?

That just changes my query parameters to look like this:

f:json
where:time > date'2/4/2014'
returnGeometry:true
spatialRel:esriSpatialRelIntersects
outFields:*
outSR:102100

It still fails.

What is the acceptable way to query features by date? I haven't found any examples.

[JakeSkinner]

I believe so.  Querying a REST service worked for either method:

Date = date '05/16/2014'

or:

Date = date '2014-05-16'

[JimWharton]

Ok, perhaps I'm misunderstanding.

I've attempted to prepend 'date' to my queries. They're still misunderstood.

"time < date'"+this.esriDate(endDate)+"'"

Still throws an error.

I haven't found any docs telling me what date format ESRI expects.