Diff date syntax in x++

(0) Share

Report

Posted on by ambika sowmyan

365

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

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Denis Patrakov on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Suggested answer

Divya Lakshmi 745 on at

Like (1)

Report
Copy link

Link copied!

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));

}

Was this reply helpful? Yes No
Suggested answer

Anoop Tripathi 790 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No