OQL Date Parameter

0
Hi there, I can’t figure out how to compare time with parameter. I am testing OQL in dataset. $StartDate is a parameter in “Date and time”. However once I create this parameter, Mendix make it to “Date and time range” type. When I use it in below OQL, Mendix give me 2 errors:  “Incompatible expression types: Date and time range, Date and time” and “Expression must be of type Interger or Float or Decimal or String or DateTime”. I am every confused by this error.  Could you please help on how to make time comparison with parameter in OQL? I found it very difficult to work with OQL in mendix. What I want is just a report, but report widget only support dataset, which is OQL. Any other suggestion?   select o.ProcessName  from OrderDispatch.OrderLine ol join ol/OrderDispatch.OrderLine_Order/OrderDispatch."Order" as o where $StartDate <= o.createdDate  
asked
3 answers
3

Is $StartDate a vlaue of type DateTime?

There is a test project linked in the Mendix Documentation, maybe you can doublecheck there your case.

answered
3

An option that works is to convert your date to a long. OQL can compare dates from the database with long values. There is a Java action in CommunityCommons called DateTimeToLong that you can use to convert it.

answered
3

***Editted since my first remarks was not correct***

This is how to do it: First convert the Date-parameter to a string ($Startdate) using this format

formatDate($Query/DateParameter,'yyyy-MM-dd')

Next step: feed that string to another string holding the OQL statement.

'select * from "Sales.Order" where Date < '''+$StartDate+''';'

Then feed the resulting statement to the OQL:

select * from "Sales.Order" where Date < '2020-07-09';

OQL want an attribute on the left expression part

I extended the OQL-examples on page https://mydemoversion8-sandbox.mxapps.io/p/OQL with an example showing this behavior and taking in a Date-parameter. 

Also added an example for a DateTime parameter.

answered