Greater than date in Axapta 2009 X++

1
votes

I am looking to write a query that will select all Sales Orders greater than a certain date in X++. This would be part of a job that I am writing.

Something like this: 

SalesTable.createdDateTime > '2016-09-01'

I believe that I need to use DateTimeUtil::newDateTime to do it. I've seen examples like:

where salesLine.createdDateTime == DateTimeUtil::newDateTime(systemdateget(), 0)

How do I put an actual date in there? Or should I be using a different method? QueryBuilder perhaps?

Many thanks,

Anna

2
axaptax++dynamics-ax-2009
Hi Anna, to get actual date you can use DateTimeUtil::getSystemDateTime() or DateTimeUtil::utcNow() methods or today() method If you only want to date. - Jonathan Bravetti
Thanks Jonathan, but how do I use that actual date in the code? I've seen various examples that has today's date - 30 (for the past month's sales orders), but what I would like to do is select all records after the 15th September (and not have that date change depending on today's date). - Anna Dougherty
Try using mkdate (msdn.microsoft.com/en-us/library/aa871977.aspx) - Pradeep Muttikulangara Vasu

2 Answers

1
votes

Using AX's query syntax or using a query object all depends on your use. Doing a date/time is useful for either.

Here is an example job that shows a more correct way to do what you want and also shows timezone considerations.

You can build a date object or a utcDateTime, which is more likely what you'll need to do.

static void DateTimeExample(Args _args)
{
    utcDateTime         myDateTime;
    date                myDate;
    TimeOfDay           myTimeOfDay;

    // Set your date
    myDate          = mkDate(1,10, year(today())); // Ex. October 1, 2016

    // Set time of day or just use 0
    myTimeOfDay     = DateTimeUtil::time(DateTimeUtil::utcNow());

    // Build your utcObject
    myDateTime  = DateTimeUtil::newDateTime(myDate, myTimeOfDay);

    // Apply timezone offset
    myDateTime  = DateTimeUtil::applyTimeZoneOffset(myDateTime, DateTimeUtil::getCompanyTimeZone());

    info(strFmt("utcDateTime: %1", myDateTime));
}
1
votes

By your comment (but how do I use that actual date in the code?) to use this in a query you can use this code:

Query                q;
QueryRun             qr;
QueryBuildDataSource qbd;
QueryBuildRange      qbr;
utcDateTime          mutcDateTime;
date                 mDate;

;

mDate        = str2Date("15/09/2016", 123); //15h September   
mutcDateTime = DateTimeUtil::newDateTime(mDate,0);

q   = new Query();
qbd = q.addDataSource(TableNum(SalesTable));
qbr = qbd.addRange(FieldNum(SalesTable, CreatedDateTime));
qbr.value(strFmt('>%1', mutcDateTime));
qr  = new QueryRun(q);

To run this query by code and get SalesTable:

while(qr.next()){
    salesTable = qr.get(tablenum(SalesTable));
}