Query Task with Oracle date

Question

Hi

I'm having issues with a query task against data in an Oracle SDE

I have tried

queryTask = new esri.tasks.QueryTask("http://........../MapServer/0");

//build query filter
        query = new esri.tasks.Query();
        query.returnGeometry = false;
        query.outFields = ['*'];
        query.where = "DATE_CREATED > TO_DATE('2004-10-04 00:00:00','YYYY-MM-DD HH24:MI:SS')";
        queryTask.execute(query, showResults);

But get  -

dojo.io.script error 
Error {code: 400, message: "Unable to complete operation.", details: Array[0], log: undefined}

not sure what else to try, the query as shown works fine in ArcMap (where I copied it from)

Cheers

ACM

MohammedEl_Shafaey · Answer

Hi Andrian,

Try this

query.where = "DATE_CREATED > to_date('10/04/2004 00:00','MM/DD/YYYY HH24.MI')";

SadanandacharB1 · Answer

To include the date in where clause need to use standard sql syntax, and also it will not support sub queries, I had the same issue in SL api & Arcgis server 10.2.1, it solved when I changed like below :

featureLayer.Where = "SURVEY_DATE >= date '" + dpFromDate.SelectedDate.Value.ToString("yyyy-MM-dd")

+ "' and SURVEY_DATE <= date '" + dpToDate.SelectedDate.Value.ToString("yyyy-MM-dd") + "'";

for more info ref the link, this may help  ArcGIS Help (10.2, 10.2.1, and 10.2.2)

AdrianMarsden · Answer

Thanks for that - we do use 10.1, but alas the dirtying of the query still produced the same error.  Changing the query to a simple text based comparison does work  (so, refval = "1234/abc" works)

Cheers

Acm