web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / AOT Query Range todate...
Microsoft Dynamics AX (Archived)

AOT Query Range todate() and null and date between

(0) ShareShare
ReportReport
Posted on by MAKDAX2012_Ahmed 110

Hello guys 

I am facing a problem in applying filter (range) in AOT Query todate(),null values, date range between(and pt.DELIVERYDATE between pt.DELIVERYDATE and pt.ConfirmedDlv) look below picture 

and in other words i want to translate T-SQL query in to AOT query 

"select count(QCINSPECTOR) from VENDRECEIPTSLISTJOUR vrj
inner join purchtable pt on pt.purchid=vrj.PURCHID
where QCINSPECTOR='' and cast(vrj.RECEIPTSLISTDATE as date)=cast( GETDATE() as date)
and pt.PurchStatus=1 and pt.DELIVERYDATE between pt.DELIVERYDATE and pt.ConfirmedDlv"

 

PreIGP.GIF

preIGP2.GIF

preIGP3.GIF

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    BrandonSA Profile Picture
    BrandonSA 1,673 on at
    RE: AOT Query Range todate() and null and date between

    Hi

    What exactly is your problem? Are you getting an error?

    Should you be using this class instead? docs.microsoft.com/.../gg964746(v%3dax.60)

    Check here:

    community.dynamics.com/.../196953

  • Suggested answer
    Jie G Profile Picture
    Jie G on at
    RE: AOT Query Range todate() and null and date between

    Hello,

    Do you have any build error? The DateTimeUtil::day requires a parameters of utcdatetime type. technet.microsoft.com/.../datetimeutil.day(v=ax.10)

    You can try to use Day(0) as value in data range properties. Positive values indicate future dates, and negative values indicate past dates. 0 indicate today.

  • MAKDAX2012_Ahmed Profile Picture
    MAKDAX2012_Ahmed 110 on at
    RE: AOT Query Range todate() and null and date between

    Thanks day(0) works but how i can apply date range  pt.DELIVERYDATE between pt.DELIVERYDATE and pt.ConfirmedDlv in AOT Query.

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: AOT Query Range todate() and null and date between

    You can't. But you can assign such range with x++.

    But what kind of range is "pt.DELIVERADATE between pt.DELIVERYDATE and pt.ConfirmedDlv"? So delivery date must be between delivery date and confirmed delivery date? What kind of result do you expect from that?

  • MAKDAX2012_Ahmed Profile Picture
    MAKDAX2012_Ahmed 110 on at
    RE: AOT Query Range todate() and null and date between

    Actually I want to receive all the Purchase order between these two dates DELIVERADATE  and ConfirmedDlv and i want to show cue on EP where AOT Query range is  all open purchase order between these dates.

    BR

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: AOT Query Range todate() and null and date between

    I'm still not sure I understand. But do you understand that your "pt.DeliveryDate between pt.DeliveryDate and pt.ConfirmedDlv" doesn't make much sense since it's just referencing itself?

    If "between" includes also the delivery date and confirmed delivery date, you would get all records from PurchTable.

    If "between" means that only purch orders where delivery date is greater than delivery date should be retrieved, you will get no records at all.

  • MAKDAX2012_Ahmed Profile Picture
    MAKDAX2012_Ahmed 110 on at
    RE: AOT Query Range todate() and null and date between

    please guide me what is syntax of   DeliveryDate <=ConfirmedDlv   in  Query Range

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: AOT Query Range todate() and null and date between

    Check this article: [View:https://docs.microsoft.com/en-us/dynamicsax-2012/developer/using-expressions-in-query-ranges]

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Community Member Profile Picture

Community Member 4

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#3
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans