Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Diff date syntax in x++

Posted on by 365

Hi

I am creating a SSRS report using RDP. In the report business logic i am trying use a similar sql in the report process method I have a nested case statement in sql which i need to use it in x++.  I am trying to do a date diff on two columns from AX tables and comparing the result with custom colum in one of the ax table.. I am using the switch stayements for the case statement but am stuck on the datediff part ...any help on the syntax for the case statement is greatly appreciated!!

 

 

 

 

 

 

 

 

 

 

select
vps.origpurchid as PO,
vps.Purchaselinelinenumber as Line,
pt.orderaccount as Vendor,
pt.purchname as VendorName,
vps.itemid as Item,
vps.name as Description,
vps.ordered as Ord,
CASE when (
CASE when
(datediff(dd,convert(varchar(10),vps.deliverydate,111), convert(varchar(10),GETDATE(),111)) ) >  vt.DELIVERYDATEDAYSTOLERANCE
then '0'
else '1'
end)!=
(case when (vps.ordered/vps.qty) * 100  > (vt.DELIVERYDATEDAYSTOLERANCE)/100
then '0' else '1'
end )then '0' else '1'
end otif
from
 [vendpackingsliptrans] vps
join [purchtable] pt
on vps.origpurchid = pt.purchid
join [purchline] pl
on pt.purchid = pl.purchid
join VENDTABLE vt
on  pl.VENDACCOUNT = vt.ACCOUNTNUM

*This post is locked for comments

  • Suggested answer
    Anoop Tripathi Profile Picture
    Anoop Tripathi 790 on at
    RE: Diff date syntax in x++

    I do not think you are clearly asking what you trying to achieve. As you have mentioned you are using RDP class in Ax 2009.

    Whatever be the case as suggested by Alan try to use DateTimeUtil class also please do take note conversion of date should not be done using simple varchar etc.

    In order to better understand where you are going wrong in dates I would suggest using info to find what is actually in the variable and in case of report please hit and try by printing on screen different variable states ( before/after comparison).

    Do clarify on the issue more so we can help you accurately.

  • Suggested answer
    Divya Lakshmi Profile Picture
    Divya Lakshmi 745 on at
    RE: Diff date syntax in x++

    Hi,

     Do you need SQL code for date diff to use in X++ editor? or you need date diff code expression to use in SSRS reports?

      Simple X++ code to find date difference is below:

      static void DateDiff(Args _args)

    {

       TransDate   d1,d2;

       int daysdiff;

       ;

       d1  = 20\11\2016;

       d2  = today();

       daysDiff = d2 - d1;

       info(strfmt("%1",daysDiff));

    }

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Diff date syntax in x++

    Hi,

    I think you should look at the DateTimeUtil class in the system documentation node.

    You can find there some nice methods that you can use inclusively for datediff.

  • Suggested answer
    Re: Diff date syntax in x++

    I don't think you should convert dates to varchars before passing them as arguments to datediff().

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 229,918 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans