How do you format DateTime field in OQL for dataset?

0
Trying to display a date and time for a report I’m building, and I can’t format the datetime within the Report grid, so I have to format it within the OQL query. There doesn’t appear to be a good way to emulate the following, which is how I’d do this in SQL Server: FORMAT(DATETIMEFROMPARTS(2022,10,3, 13, 2,0,0),'MM/dd/yyyy hh:mm tt')   I tried to emulate it using some CASE WHEN logic but it’s so ugly, and the time isn’t padded properly. I also can’t nest another CASE WHEN (For some reason??) in order to pad minutes as necessary. (example below of improperly padded minutes) CASE WHEN DATEPART(HOUR, HearingDate) > 12 THEN CAST(CAST(DATEPART(HOUR, HearingDate) AS INTEGER)-12 AS STRING)+':'+DATEPART(MINUTE, HearingDate) +'PM' WHEN DATEPART(HOUR, HearingDate) = 0 THEN '12:'+DATEPART(MINUTE, HearingDate)+'AM' WHEN DATEPART(HOUR, HearingDate) = 12 THEN '12:'+DATEPART(MINUTE, HearingDate)+'PM' ELSE DATEPART(HOUR, HearingDate)+':'+DATEPART(MINUTE, HearingDate)+'AM' END AS HearingTime   Anyone have a solution to this problem?
asked
2 answers
1
CAST (w.DateField AS String) as DateField

https://docs.mendix.com/refguide/oql-cast/

answered
0

Because OQL is mostly used internally the formatting is done in the Non-Persistant entitties orthe UI/ Presentation layer. As an alternative you can create a string attribute with the formatted time.

answered