OQL-problems: how to compare DateTime with DateTimeRange and String with Enum?

6
I have an OQL query in which I want to compare a DateTime attribute with a DateTimeRange input parameter (Attr. >= Param.). The OQL dataset editor shows the error "Expression must be of type integer or float or Datetime". Is there a way to render the DateTimeRange as a DateTime field? Or is there another way to perform the comparison in OQL? I noticed that statements like formatDateTime are not allowed. Also in OQL, I want to compare a String to an Enumeration input parameter (String LIKE Enum). The LIKE command does not work, and I can't use toString for the enumeration... Any tips on this? This is the used OQL: "WHERE tbl_retour.Reported = 'No' AND tbl_rtype.RetourType LIKE $retourtype" Thanks in advance.
asked
2 answers
7

To use a DateTimeRange in a comparison, use the IN operator. Mendix will automatically use the correct upper and/or lower bounds restrictions, depending on which of the 'from' and 'to' fields the user selects (so, to create your "Attr. >= Param." restriction, select a 'from' date and leave the 'to' date empty).

As to comparing a String attribute to an enumeration parameter, I'm not sure why you'd want this, but perhaps you can use the CAST function to get the result you want.

Here's an example showing the use of both IN and CAST:

SELECT
    user/Name AS UserName,
    user/LastLogin AS LastLogin
FROM
    System.User AS user
WHERE
    user/LastLogin IN $dateRange AND
    user/Name = CAST($enumParam AS STRING)
answered
0

There is a further problem with OQL dates. I believe the time part of the date is set to zero hours, zero minutes.

Both snippets of code below have the same issue: ... AND devs.ContractDate IN $datetest

... and devs.ContractDate >= RANGEBEGIN($datetest) AND devs.ContractDate <= RANGEEND($datetest)

The RangeEnd ($dateTest) is set to the start of day, so any data with dates equal to $dateTest are not extracted because the time part of devs.ContractDate is greater than the time part of RANGEEND($datetest).

As OQL does not allow date calculations to add 1 day, there is not a way of making the OQL work, except to tell the users to set the end date as 1 day later (which is not user frinedly).

answered