emeadaxsupport: Passing UtcDateTime values in extended query syntax

[Blog bot]

Источник: http://blogs.msdn.com/emeadaxsupport...ry-syntax.aspx
==============

Recently I was looking into the issue of passing UtcDateTime values into a query in X++ using extended query syntax.

First I will just clarify what I mean when saying "extended query syntax":


This is extended query syntax:
('(validUntilDate = %1)', DateTimeUtil::toStr(utcRefDateTime));

This is the regular syntax for ranges:
('%1', queryValue(utcRefDateTime));
As you can see in my examples above, for the regular syntax using queryValue() is fine, but when using the extended query syntax, there are 3 basic rules for utcDateTime values:
- it's necessary to use DateTimeUtil::toStr() to pass utcDateTime values.
- The query string needs to have brackets around it.
- No speechmarks/quotations should be used around the utcDateTime value.


So working example:
queryStr = strfmt(@"((StartDate < %2) && (EndDate < %2) && (validUntilDate = %1))", DateTimeUtil::toStr(2010-01-10T14:00:00), DateTimeUtil::toStr(2010-01-10T17:00:00));


And a failing example with brackets missing:
queryStr = strfmt(@"((StartDate < %2) && EndDate < %2 && (validUntilDate = %1))", DateTimeUtil::toStr(2010-01-10T14:00:00), DateTimeUtil::toStr(2010-01-10T17:00:00));

Failing with speechmarks/quotations:
queryStr = strfmt(@"((StartDate < %2) && (EndDate < '%2') && (validUntilDate = %1))", DateTimeUtil::toStr(2010-01-10T14:00:00), DateTimeUtil::toStr(2010-01-10T17:00:00));

There is an exception to these rules, that is when using 1900-01-01T00:00:00. Most functions in X++ are returning this as NULL when converting to a string but for the extended range syntax to work correctly we need it to be returned as a string, so the following functions cannot be used:

QueryValue()
dateTime2Str()
DateTimeUtil::toStr()
Global::utcDateTimeNull()
DateTimeUtil::minValue()


So in my environment I have introduced a new global function to make it easier for me to convert utcDateTime values to strings for extended query ranges, called dateTime2strQuery()

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

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

return cvtDateTime;
}



So now my working example looks like this:
queryStr = strfmt(@"((StartDate < %2) && (EndDate < %2) && (validUntilDate = %1))", dateTime2strQuery(2010-01-10T14:00:00), dateTime2strQuery(2010-01-10T17:00:00));


Источник: http://blogs.msdn.com/emeadaxsupport...ry-syntax.aspx