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

Notifications

Announcements

No record found.

News and Announcements icon
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

    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

    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

    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

    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

    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

    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

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

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at

    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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans