AX 2012 AX 2009 - Design AND Query to fetch records AX2012 AX2009

This post describes how to construct AND query using createddatetime and modifieddatetime fields.

1. First add this method in Global class

static str dateTime2strQuery(utcDateTime _utcDateTime)
{
    str cvtDateTime;
    ;

    if (_utcDateTime == DateTimeUtil::minValue())
        cvtDateTime = "1900-01-01T00:00:00";
    else
        cvtDateTime = DateTimeUtil::toStr(_utcDateTime);

    return cvtDateTime;
}

2. Go to your method

str strQueryVal;

strQueryVal = strFmt("(((%1.%2 >= %4) && (%1.%2 <= %5)) || ((%1.%3 >= %4) && (%1.%3 <= %5)))",
                    this.queryRun().query().dataSourceTable(tableNum(InventSettlement)).name(),
                    fieldStr(InventSettlement, createdDateTime),
                    fieldStr(InventSettlement, ModifiedDateTime),
                    dateTime2strQuery(datetimeutil::newdatetime(startDate,0)),
                    dateTime2strQuery(datetimeutil::newdatetime(endDate,86399)));

//And pass the above string value to query range value
this.queryRun().query().dataSourceTable(tableNum(InventSettlement)).addRange(fieldNum(InventSettlement, dataareaid)).value(strQueryVal);

Happy DAXing.

Cheers,
Arun