Date Functions & Date Conversions in AX

(0) Share

Report

Posted on by D365 beginner

634

Hi Experts,

I have a requirement for a report that I need to fetch the last record from a table based on the inputs provided which are Month & Year.

Example If user select the Year as 2021 and Month as August it has to look the field ToDate in my table and fetch the last record (The last record by field ToDate and there will not be multiple records for the same date)created in the Month of August 2021.

DataTypes:- Year - Yr,

DataTypes:- Month - MonthOfYearId

DataTypes:- ToDate - TransDate.

Can anyone help me with a select statement to filter the last record in the Table as per the above scenario.

Thanks in advance.

I have the same question (0)

All responses (1)

Answers (1)

Verified answer

Martin Dráb 237,967 Most Valuable Professional on at

Like (1)

Report
I would use the imput to create a date value representing the first date of the next month:

date d = mkDate(1, month 1, year);

And then I would look for values smaller than this date. You'll also need to sort the query by ToDate and take just a single record.

For example, this is how it would be done in an X select statement:

select firstOnly myTable order by ToDate desc where myTable.ToDate < d;

Was this reply helpful? Yes No