You’re offline. This is a read only version of the page.
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
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.
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));
}
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.
I don't think you should convert dates to varchars before passing them as arguments to datediff().
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,113
Super User 2024 Season 2
#2
Martin Dráb
229,918
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (