Your query is malformed with a date in where clause

Question

I'm trying to query SharePoint using jQuery and SPServices. When I run the code below, I get the error Your query is malformed. Please rephrase your query.

var searchq = "Write=02/27/2014..05/27/2014";
var q = "SELECT Size, Rank,Path,Title,Write FROM scope() WHERE " + searchq + " AND ( (\"SCOPE\" = 'Contracts') ) ORDER BY Rank DESC";

var queryText = "<QueryPacket xmlns='urn:Microsoft.Search.Query' Revision='1000'>" 
queryText += "<Query>" 
queryText += "<Context>" 
queryText += "<QueryText language='en-US' type='MSSQLFT'>" 
queryText += q;
queryText += "</QueryText>" 
queryText += "</Context>" 
queryText += "<IncludeSpecialTermResults>true</IncludeSpecialTermResults>"
queryText += "</Query>" 
queryText += "</QueryPacket>"; 

 $().SPServices({  
    operation: "Query",
    queryXml: queryText,  
    completefunc: function(xData, Status) {  
      $(xData.responseXML).find("QueryResult").each(function() {  
            //let's see what the response looks like  
            $("#hcg_expiring_contracts").html("<p>" + $(this).text().replace(/</g,"&lt;") + "</p>");  
        });  

    }  

}); 

When I remove searchq from the query, the query succeeds. I've also tried writing searchq as

var searchq = "(Write>=" + strBeginDate + " AND Write<=" + strEndDate + ")";

But still get the same error. I've tried several different date fields, but they all return the same error. How do I limit my query using these dates?

Answer 1

Should your query type be STRING and not MSSQLFT (SQL Syntax is deprecated). I assume you are using SharePoint 2010.

For the KQL, change the query to something like write:02/27/2014..05/27/2014 or write>02/27/2014 AND write<05/27/2014 (either should work). Did you know you can also use cool variables like write:"last month" according to Michael Svenson: http://techmikael.blogspot.com/2014/03/s15e01-kql-basics.html

Answer 2

I found the problem. The date is expected to be in the format yyyy-mm-ddThh:mm:ssZ, and searchq needs to use

var searchq = "(Write>='2014-02-26T00:00:00Z' AND Write<='2014-05-26T00:00:00Z') AND