Use

GROUP BY DATEPART(DAYOFYEAR, YourDate)

But add some logic for a readable presentation because 1-365 is not a common used date format.

You can try grouping by multiple criteria:

GROUP BY DATEPART(YEAR, yourDate), DATEPART(DAY, yourDate)

Performance-wise, grouping by functions of attributes can become slow because in that case indexes are not used.

Another option would be `GROUP BY DATEDIFF(DAY, yourDate, '0')` or, if you want a different time zone, then for example `GROUP BY DATEDIFF(DAY, yourDate, '0', 'Atlantic/Bermuda')`

0 here is Unix timestamp 0, which is 1 January 1970.

SELECT COUNT(*)
FROM Module,Entity
GROUP BY DATEDIFF(DAY, yourDate, '0')

If you go with this scenario, the problems start when you try to also output the column in the result:

-- this will not work
SELECT COUNT(*), DATEDIFF(DAY, yourDate, '0')
FROM Module,Entity
GROUP BY DATEDIFF(DAY, yourDate, '0')

The problem here is that databases do not allow selecting values that are not present in GROUP BY, and they do not recognize that this is the same function because of the constant '0'

GROUP BY implementations for different databases is in general not always consistent. So if you use HSQLDB, PostgreSQL, or MySQL as your database engine, the following is allowed. But Oracle, SQL Server and SAP HANA will not allow it.

SELECT COUNT(*) AS C, DATEDIFF(DAY, yourDate, '0') AS D
FROM Module,Entity
GROUP BY D

See the documentation here: https://docs.mendix.com/refguide/oql-datepart/

So use something like DATEPART(DAY, yourDate)

Regards,

Ronald

Check the OQL-demosite on service.mendixcloud.com and go to OQL-playground:

There you can find examples and test your solution.

See example under button :

FROM Sales.Customer AS c
GROUP BY DATEPART(WEEKDAY,Birthday)
SELECT DATEPART(WEEKDAY,Birthday) AS BirthWeekday_Sundayis1, 
              count(*) as NumOfPersons
ORDER BY BirthWeekday_Sundayis1 ASC