Query range value expressions can be used in any query, where you need to express a range that is more complex than that made possible with the usual dot-dot notation.
Especially to use Or clause in query, you definitely need to use Query Range Value Expression.
Today I just want to investigate more about the date data type in Query Range Value Expression.
To retrieve records where the transaction date is before 21st June, 2006. You can use Date2StrXpp() to format the date:
queryBuildRange.value(strFmt(‘(TransDate < %1)‘, Date2StrXpp(21062006)));
or to format the date using date literals directly:
queryBuildRange.value(strFmt(‘(TransDate < %1)‘, @‘2162006’)));
Besides the expression mentioned above, there is a third way to specify date data type in Query Range Value Expression, even it does not quite make sense.
In Dynamics AX, date data type is represented as 32-bit (4 byte) integer. The second byte, the third byte and the last byte store the information of the day, the month and the year respectively.
So in Query Range Value expression, you can use the following instead to allow AX to transform the date directly,
Date transDate = 2162006;
int dayInt, monthInt, yearInt;
;
dayInt = dayofMth(transDate);
monthInt = mthofyr(transDate);
yearInt = year(transDate);
//dateInt should be integer type value rather than real type value
//otherwise AX can’t figure out!!
dateInt = (dayInt-1)*Power(2,16) + (monthInt-1)*Power(2,8) + (yearInt-1900);
queryBuildRange.value(strFmt(‘(TransDate < %1)‘, dateInt));
Honestly, this is a bit complicated, but just want to show you how AX holds the date value. Actually, I seldom use the third expression in my codes.
And that is the reason why the scope of date data type is from 111901 to 31122154.
(In AX 3.0, max date is 31122153, and 31122154 in AX 4.0)
Because AX can only hold dates for up to Power(2, 8), which is 256 years (1900 to 2155).
Fred Shen's AX weblog 