Last time I looked at aggregates, but unforgivably I missed out how to apply grouping to date/time attributes:

Grouping works quite simply for data types such as text, picklist or lookup values, but a simple grouping of date/time values wouldn’t work very usefully. They are stored with second (or in some cases, millisecond) precision, so in most cases no two records will have exactly the same value and each record will end up in its own group.

More typically you’ll want to group records that have a value in the same day or month to generate summary charts or reports, and that’s exactly what FetchXML does for you using the dategrouping attribute:

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="contactid" alias="occurrences" aggregate="count" />
      <condition attribute="statecode" operator="eq" value="0" />
    <order alias="occurrences" descending="true" />

This example will get the number of contacts created each month.

Available Groupings

Date/time values can be grouped by:

  • day
  • week
  • month
  • quarter
  • year
  • fiscal period
  • fiscal year

If you are grouping by a date/time column, you must use one of these groupings. You can’t group by the raw date/time value – if you try you’ll get the error Invalid value '' for dategrouping.

You’ll notice that there’s no support for anything more precise than a day – you can’t group by hours for example. If you have a need for that you’ll need to retrieve each individual record and implement your own grouping logic.

When you group by day/week/month/quarter, you get the appropriate day/week/month/quarter number. For example, when grouping by month you get a month number 1 – 12. Note that this means records in the same month from different years will be included in the same month. If this isn’t the behaviour you want, e.g. you want to keep January 2019 separate from January 2020, you need to include a separate grouping by year as well:

<fetch aggregate="true">
  <entity name="contact">
    <attribute name="createdon" alias="year" groupby="true" dategrouping="year" />
    <attribute name="createdon" alias="month" groupby="true" dategrouping="month" />
    <attribute name="contactid" alias="occurrences" aggregate="count" />
      <condition attribute="statecode" operator="eq" value="0" />
    <order alias="occurrences" descending="true" />

For week grouping you get a number 1 – 53, quarter is 1 – 4 and day is 1 – 31.

SQL Equivalents

These date/time grouping options are equivalent to using the DATEPART function in SQL:

SELECT   DATEPART(month, createdon) AS month,
         count(*) AS count
FROM     contact
GROUP BY DATEPART(month, createdon)

There’s no direct SQL equivalent for the fiscal period / year grouping options as these are application specific. However, in SQL 4 CDS I’ve extended the DATEPART function to allow fiscalperiod or fiscalyear to be specified as the part type name parameter.

You’ll need SQL 4 CDS 1.0.5 (which I should be releasing soon) or later to use the DATEPART function.

10 thoughts on “Inside FetchXML pt 6 – datetime grouping”

  1. Hi, very nice article, really helped me. A question I have is, if I need to group by DAY, it’s all clear and it works. What if I need to include the actual DATE in the resultset? From what I understand, I cannot do it unless there’s another trick?
    For example, if I group by DAY, I get DAY (integer) as part of the resultset. But I also need to see the entire DATE. I understand I can group by DAY, MONTH, YEAR and then put them all together to get the actual DATE, but that would require a 2nd step. Is it possible to do it all in 1 step?

    Best regards,

  2. Hi Mark,

    Your blogs have been very helpful!
    I am grouping results by month and year as well as location. How can count = 0 be returned when I have no records in a month and year with location “A”?

    For example:
    1-2021, Location: Seattle, Count: 189
    1-2021, Location: New York, Count: 122
    2-2021, Location: Seattle, Count: 200
    2-2021, Location: New York, Count: 0

    New York as no records in February 2021 and so that row doesn’t come back from the query. I tried using countcolumn but that didn’t help either!

    Thank you!

    1. It’s only possible to include results that actually exist in your database, so it’s not possible to do this directly. Your main two options are either to insert the missing values after you have run the query, or insert a phantom record for each location and each month to ensure there is some data for the query to work with. You’d then also have to change the “count” to “sum” and select a number column that is set to 1 for all of your “real” records and 0 for the phantom ones.

  3. please show how you would retrieve the date grouping in c# code using the alias, i’m getting 01/01/0001 when i try to retrieve it.

  4. Hi
    If I want to do my GROUP BY condition,
    For example, I want to group into 3 groups only, and not according to the existing options
    I want groups by:
    less than 24 hours
    More than 24 hours and less than 48 hours
    more than 48 hours,
    Is there a way for me to do this?
    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.