Date data type in Query Range Value Expression

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)‘, @‘216\2006’)));

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 = 216\2006;
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 1\1\1901 to 31\12\2154.

(In AX 3.0, max date is  31\12\2153, and 31\12\2154 in AX 4.0)

Because AX can only hold dates for up to Power(2, 8), which is 256 years (1900 to 2155).


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: