Query 'OR' range for Date in Dynamics AX

The query build range for 'OR' conditions in Dynamics AX tests your patience and skills 😄

One of the reasons is that it's tough to know what went wrong.

Some the rules has been listed out here.

I followed the such an approach for building query.

The functionality was to filter records based which falls in today's range.

Let's say today's date is 27/11/2019. So here I have to fetch all the records where StartDate >= today() AND EndDate<=today(). Simple right? But the twist is that if EndDate is null() I have to consider it as limitless range. Meaning I have to consider records where (EndDate<=today() || EndDate = DateNull()) too in my range.

The code below filters the records equal/below today's date.

qbds.addRange(fieldNum(Table, StartDate)).value(strFmt('<%1', systemDateGet()+1));

For some reason, the code below did not consider when the EndDate was null. Query extended range failure: Syntax error near 41 was the error thrown.

//qbds.addRange(fieldNum(Table, EndDate)).value(strFmt('((%1 > %2) || (%1 = %3))',fieldStr(Table, EndDate), systemDateGet()-1, DateNull()));

I tried a few things here. One of them was to constructed a date variable of value (2,1,1900) and trying to filter records below this date(Which is the value of dateNull()- 1/1/1900).

//qbds.addRange(fieldNum(Table, EndDate)).value(strFmt('((%1 > %2) || (%1 <%3))',fieldStr(Table, EndDate), systemDateGet()-1, DateNull()+1));

Finally the approach below worked. I split the OR condition into two AND's.

qbds.addRange(fieldNum(Table, EndDate)).value(SysQuery::value(dateNull()));
qbds.addRange(fieldNum(Table, EndDate)).value(SysQueryRangeUtil::greaterThanDate(-1));