Date comparison in XPath

1
Hi, I am trying to retrieve objects from a database that comply to the following constraint: firstActionDate=NULL or firstActionDate is more than 5 days ago. I tried to build an XPath constraint, but it keeps giving errors. My first option is: [firstActionDate=NULL or daysBetween('[%CurrentDateTime%]', firstActionDate)>5] This gives: Expression must be of type Float or Integer or String or DateTime. Incompatible expression types: Boolean, Integer/Long. Unknown function 'daysBetween'. Then I found the following question and tried that: https://community.mendix.com/questions/5/using-date-subtraction-in-Xpath [firstActionDate=NULL or firstActionDate<'[%CurrentDateTime%]-5*[%DayLength%]'] This leads to 'Unknown token '[%CurrentDateTime%]-5*[%DayLength%]' How should I write the XPath constraint to achieve my condition?
asked
4 answers
6

Hai Joost,

You can't use tokens like that in a xpath retrieve from a database. You need to create a variable and check that against your database. Your xpath will be something like:

[firstActionDate=empty or firstActionDate< $FiveDaysAgo]

Before you do your retrieve you need to create a datetime variable, which is 5 days ago. You can use this expression:

addDays([%CurrentDateTime%],-5)

This way you create a datetime with the value of 5 days ago and store this one in a variable. This variable can be used in a xpath retrieve and tokens cannot, because the xpath retrieve will be translated to a SQL query.

answered
8

I think your ' ' are wrong:

[createdDate < '[%CurrentDateTime%] - 5 * [%DayLength%]'  ]

is a valid xPath constraint in my case

answered
1

Although this is quite an old topic, I would like to add something that tripped me up and that is that you MUST use spaces around the operators if you want to use period related tokens.

I found this in a fairly recent comment by Frank Wagenaar in a very old topic ( https://forum.mendix.com/link/questions/5 )

answered
0

I struggled with this too.. XPATH supports a long(ish) list of Date / Duration functions, but Mendix offers only a limited subset of them. http://www.w3.org/TR/xpath-functions/#component-extraction-functions

I worked around this, in somewhat dirty manner, but creating a "DateTimeHelper" entity - a database table with just one record, which gets updated every X hours (or when you need those numbers). Next, create a many:one relation from your table to this record. And, use the following in XPATH -

[myDate > Module.MyTable_DateTimeHelper/Module.DateTimeHelper/5DaysBeforeToday ]

Limitation: the "5Days" is kind of fixed. You can't make 5 a user changeable parameter.

answered