How to write “Between” a query expression for today's date and two date attributes for an object in one condition?

Question

I have a new_Term object with the attributes new_StartDate and new_EndDate. I really want to write this statement in a single query expression:

new_StartDate <= DateTime.UtcNow <= new_EndDate 

My first idea would be to use Inter ConditionOperator, but only one attribute works between statements, i.e.

 // Not valid because the first parameter expects a string attribute name, not a value new ConditionExpression(DateTime.UtcNow.Date, ConditionOperator.Between, "new_startdate", "new_enddate") 

Besides the obvious two condition expressions, is there a way to do this in a single condition expression?

 new ConditionExpression("new_startdate", ConditionOperator.LessEqual, DateTime.UtcNow); new ConditionExpression("new_enddate", ConditionOperator.GreaterEqual, DateTime.UtcNow); 

Answer 1

I believe that you have all the possible answers at the bottom of your question:

 new ConditionExpression("new_startdate", ConditionOperator.LessEqual, DateTime.UtcNow); new ConditionExpression("new_enddate", ConditionOperator.GreaterEqual, DateTime.UtcNow); 

The ConditionOperator enumeration is a limiting factor - regardless of whether you are writing fetchXml or using a query expression, you are stuck with the convention operators available in this enumeration . And, as you correctly pointed out, between them there are no parameters that you will need.

Only your only supported (only in place) option to further shorten this statement uses SQL to access filter views, but this is certainly too large :)

Answer 2

Have you tried in FetchXML? You may be able to do this, and then use the FetchXmlToQueryExpressionRequest to run the query. I wrote a blog post that has an example back .

Answer 3

 QE.Criteria.AddCondition("modifiedon", ConditionOperator.Between, new Object[] { first, last}); 

This works for me. the first and last variables are of type DateTime.