Grouping by date is not really a problem, just use (for example) "GROUP BY DATEPART(DAYOFYEAR, DateAttr)". The problems start when you also want to SELECT the dates that you grouped by. As it turns out, this is actually quite hard to accomplish. The obvious option is
SELECT DateAttr FROM Module.Entity AS Entity GROUP BY DATEPART(YEAR, DateAttr), DATEPART(DAYOFYEAR, DateAttr)
But this is not accepted by the database because all GROUP BY clauses must also occur in the SELECT statement. (This is the ORA-00979 error).
The solution to this is to return the date fields in separate columns:
SELECT DATEPART(YEAR,DateAttr) AS DateYear, DATEPART(MONTH,DateAttr) AS DateMonth, DATEPART(DAY,DateAttr) AS DateDay FROM Module.Entity AS Entity GROUP BY DATEPART(YEAR,DateAttr), DATEPART(MONTH,DateAttr), DATEPART(DAY,DateAttr)
If you want to have a single, formatted date field in the SELECT, you run into a different problem: there is no easy way to format the date as a String in OQL. When you manually concatenate the date fields with separators in between (e.g. '-'), the GROUP BY and SELECT clauses are not recognized by the database as the same thing anymore. The only solution at this point is to use a Java action as a data source, retrieve the data using Core.retrieveOQLDataTable() and glue the date field together yourself.