How do I return DATE instead of DATETIME in a dataset?

1
I have a dataset which returns a count of number of records by date, but since Mendix only allows DATETIME and not DATE as a datatype in the domain model, in need to truncate the database value to remove the TIME elements. Is there any way to do this in Mendix? I can't see anything in the OQL reference except for DATEPART, but this will only return a single date element, day month or year, rather than the full date.
asked
4 answers
1

Hi Andrew,

Datepart should do the trick. You can combine several datepart statements to a full date statement ( datepart(x, DAY) + "-" + datepart(x, MONTH) + ..) ) and also use it in the group by clause. So not group by ReceivedDate but group by the 3 x datepart combination expression

answered
1

Thanks Herbert. I couldn't quite get your example to work, as it appears to return datepart as an integer, so can't combine it with + "-" etc. However treating it as a number I can use the following which returns the date as yyyymmdd : (datepart(YEAR, dateReceived)10000)+(datepart(MONTH, dateReceived)100)+(datepart(DAY, dateReceived))

answered
0

It is not clear to me what you want to achieve. But if you have the right dataset you could use the formatdatetime commnand to show the results in any format you like. Or do you need the date part for a OQL retrieve action?

Beware that if you get dates from outside Mendix and the time part is not needed, to create UTC dates from the the formatdatetime results and make sure that the timepart is set to zero's. Otherwise you could create localize problems when you try to retrieve a certain date.

Regards,

Ronald

answered
0

Thanks Ronald, but formatdatetime is not available in OCL as far as I am aware. I have an Entity 'Order' in my Mendix domain model, with a dateReceived attribute. dateReceived is a DateTime datatype, so includes date and time (e.g. 2012/11/23 15:05:59'. I want to create a Mendix report to show the number of Orders received each day, based on the dateReceived. I have created a Dataset in my Mendix application, using 'group by dateReceived', whereas what I need is the equivalent of the SQL function 'group by cast(dateReceived as date)', but there doesn't seem to be any way to format it in the OCL query to return just the date element.

answered