Calculating the days in between two dates

Hi! I need your suggestion for the following request: Calculate the days between two dates at the template level in an Xpath expression.

To simplify the expression, I first tried creating a formula based on two dates from the same CollectionLetterLines record:

@CustCollectionTrans_TransDate and

But in the end, I need to calculate the day difference between the two dates below:

CollectionLetterHeader/@CollectionLetterDate and

In the attached template, I used a formula that creates a number out of both dates so that I can subtract them:

format(number(day(@CustCollectionTrans_DueDate)),‘00’))) -


As you can see, it doesn’t do the job:

The correct difference between the dates is 45 days. Where am I going wrong? Is there something I’m missing?

I noticed some time and date functions that I’ve also tried to use, but none of them work for my case.

I did my tests on the attached Replica template:
CustCollectionJour.Report_DateDifference_1.docx (83.3 KB)

Thank you very much for your help.

Hi Michael,

Calculating the date difference on the template level is not possible as the Xpath language we use does not support this.

How to calculate day difference in X++

However, it is possible to do this by introducing a custom data field in X++, so let me provide more details about this approach.

First, we need to create a new data field in the DocCustCollectionJourReportDSP class:

_addingRecord.addCalculatedField('DaysBetween', _currentLine.CollectionLetterDate - _currentLine.CustCollectionTrans_DueDate);

Once you have this in place and generate a new data source file (DDSP), it should contain the newly created @DaysBetween data field:

The last step is to add this field to the template you use for this report.

And here are the results:

I am attaching the test template below:
CustCollectionJour.Report_DateDifference_3.docx (77.5 KB)

How to compare dates in Xpath

The approach you tried to use by converting dates to numbers is only applicable for date comparisons (e.g., which date is greater), and you can see some examples in the attached template.

I used your formulas in the first three columns to compare the two dates, and the comparisons are working, as you can see.

The last two columns use a slightly different approach to using the format() function, formatting the date as yyyyMMdd:

number(format(data-source(‘MainData’)/CollectionLetterHeader/@CollectionLetterDate, ‘yyyyMMdd’))

To read the value of the @CollectionLetterDate from the current context of the List tagging element, we need to introduce the data-source() function.

Finally, the number() function converts the date formatted as yyyyMMdd into a number.

As you will see in the last column, the Xpath expression to get the value of the Due date converted to a number is:

number(format(@CustCollectionTrans_DueDate, ‘yyyyMMdd’))

The entire Xpath expression (i.e., comparison) now reads as follows:

number(format(data-source(‘MainData’)/CollectionLetterHeader/@CollectionLetterDate, ‘yyyyMMdd’)) >
number(format(@CustCollectionTrans_DueDate, ‘yyyyMMdd’))

And here are the results:

Please let me know if this has helped.