Skip to Content (Press Enter)
2018-12-04 15-40-41
Peter Travers
676Views
4Comments
Solved
Sort by month in aggregate on a date attribute
Discussion
How-to

Hello, 

Sorry for the basic question but I can't quite see how to get it to work...

In my database table there is a date attribute (YYYY-MM-DD), I want to group the dates together so I can use the data in charts/graphs. 

I want them grouped by Year and Month so 2018-01, 2018-02, 2018-03...

I have tried to reformat the date using the FormatDateTime function but get the error that the function can't be executed in the database.

Is there a way of doing this correctly?

Thanks 

Peter

0
0
11 Oct 2018
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Solution

Hi Peter,

You can use the built-in Functions Month() and Year() to Group By on those.

EDIT: It would look something like this:

1
0
11 Oct 2018
Copy Link
2018-12-04 15-40-41
Peter Travers

Kilian Hekhuis wrote:

Hi Peter,

You can use the built-in Functions Month() and Year() to Group By on those.


Thank you, haven't used much of the date functions so didn't know they were available. 

0
0
11 Oct 2018
Copy Link
2018-10-29 08-31-03
João Marques
 
MVP

Hi Peter,


In your aggregate, you can add a computed attribute to get year + date and group by  this attribute.


Hope it helps.

João

0
0
11 Oct 2018
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP

Hi Peter,

They are. That is, the built-in Functions can be used when there are database equivalents, since they need to be executed by the database.

0
0
11 Oct 2018
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.