web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / How i can "Group ...
Microsoft Dynamics AX (Archived)

How i can "Group By Cast()" in Query Datasource???

(0) ShareShare
ReportReport
Posted on by ismaelivan 578

Hi, I have consulted this forum for a long time and it has helped me a lot, but it is the first time I ask a question, hopefully they can help me.

How i can group the field with out time value, only with date?

In sql i have...

select convert(date, CREATEDDATETIME) ....
from ...
group by
CAST(CREATEDDATETIME AS DATE) .....

How i can "Group By Cast()" in Query Datasource?

I currently group as this.query().dataSourceName(this.name()).addGroupByField(fieldnum(Foli_ReleaseLogs, CreatedDateTime));

I found "SysComputedColumn::cast(field, 'date')" , But I do not know how to apply it in addGroupByField()

Sorry, my english is bad. Thank you for your answers.

Greetings from México.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 304,020 Super User 2026 Season 1 on at

    Hi ismaelivan,

    I haven't got this exact requirement. There might be two solution directions:

    - Creating a view and computed columns with T-SQL for the Cast()

    - Using direct SQL statements using the UserConnection and Statement classes.

    For another purpose I had written a blog post with an example how to run a direct SQL statement recently: kaya-consulting.com/how-to-read-uncommitted-records

    There are also many examples in Dynamics AX if you search on keywords like UserConnection or sqlStatement

  • ismaelivan Profile Picture
    ismaelivan 578 on at

    Thanks for you answer, my query is large with several joins in datasource init(), but basically have a form and my datasource is a Foli_ReleaseLogs table. I only pass two Ranges:

    this.query().dataSourceName(this.name()).addRange(fieldNum(Foli_ReleaseLogs, ItemId));

    this.query().dataSourceName(this.name()).addRange(fieldNum(Foli_ReleaseLogs, CreatedDateTime)); (i set only date range in calendar)

    and one group

    this.query().dataSourceName(this.name()).addGroupByField(fieldnum(Foli_ReleaseLogs, CreatedDateTime));

    But this groupping for datetime:

    2017-03-22 14:18:35.000 ....

    2017-03-22 14:25:43.000 ....

    2017-03-22 14:30:09.000 ....

    2017-03-22 14:33:20.000 ....

    2017-03-22 14:37:23.000 ....

    And i need this (group all by date only):

    2017-03-22 ...

    I show results in a grid.

    Is necessary a view?

    Thanks!!

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 239,084 Most Valuable Professional on at

    Create a view with a computed column with CAST(), which will represent the date without time. Then use this view in your query and filter by the computed column.

  • ismaelivan Profile Picture
    ismaelivan 578 on at

    I understand this..

    In View/Method...

    public static server str GetDateBOM()
    {

    return SysComputedColumn::cast(SysComputedColumn::returnField(
    tableStr(_plo) ,
    identifierStr(Foli_ReleaseLogs_1),
    fieldStr(Foli_ReleaseLogs_1, CreatedDateTime)),'date');
    }

    Insert a Date ComputeColumn "DateBOM" and call method GetDateBOM()

    And the range over this Computed Column "DateBom", is correct??

  • Martin Dráb Profile Picture
    Martin Dráb 239,084 Most Valuable Professional on at

    Not a range. You wanted grouping.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Joris dG Profile Picture

Joris dG 5

#2
Andrew Jones a1x Profile Picture

Andrew Jones a1x 2

#3
Basit Profile Picture

Basit 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans