News and Announcements icon

Dynamics 365 CommunityForums Finance | Project Operations, Human Resources, ... Date Functions & D...

Finance | Project Operations, Human Resources, ...

Answered

Date Functions & Date Conversions in AX

(0) Share

Report

Report

Posted on by D365 beginner

572

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.

All responses Img

All responses (1)

Answers Img

Answers (1)

Verified answer

Martin Dráb 230,188 Most Valuable Professional on at

Like (1)

Report
RE: Date Functions & Date Conversions in AX

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;

Helpful resources

Quick Links

Leaderboard

#1

André Arnaud de Cal... 291,253 Super User 2024 Season 2

#2

Martin Dráb 230,188 Most Valuable Professional

#3

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans