record age in while select

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hi all,

I am trying to create a snapshot of a vendor and put the results in a grid. I am having trouble with the part of figuring out if a record is less than two years old. I created the query first in T-SQL and tried to translate to x++ select syntax, but am having trouble finding anything that represents the SQL well in this small instance.

Here is the SQL I used in the where clause:

WHERE InvoiceDate > (DATEADD(Year, -2, GETDATE())

Is there anything like this in x++ where i can put it in the where clause? Thanks in advance for any help you can provide.

Bob

2

Hello Bob,

You can use the function mkdate to build a specific date, and the functions dayOfMth, mthOfYr an year to build it based in todays date.

For example:

date date2YearsAgo;

;

date2YearsAgo = mkDate(dayOfMth(systemDateGet()), mthOfYr(systemDateGet()), year(systemDateGet())-2);

while select table

where table.InvoiceDate > date2YearsAgo

{

//do something…

}

Best Regards,

Manuel Esquivel

3

Manuel,

This was just the thing I was looking for! Thank you very much for your help!!

Regards,

Bob

4

when i am used given bellow statement in the microsoft dynamics ax then system shows error.

error : syntax error

any one have solution

select ItemId,sum(qty) from inventtrans1 group by itemid,mthofyr(datephysical),year(datephysical);

i want to fetch sum of qty month and year and item group wise.

5

You cannot use functions within the group by. You can create a View of the InventTrans table and add two compued columns to split the Date, one column for Month and other for Year and then do your Query based on the View.

Thanks,
Manuel Esquivel