Dynamics 365 Business Central: Using the Date Method to retrieve only the Day, Month, and Year Data in Queries [Group by Year-month]

Hi, Readers.
I was asked last week that a customer needs the data to be listed under the Year-Month group, is there any easy way to do it in Business Central?

For example: For data in Item Ledger Entries

So in this post, I would like to talk about how to use the Date Method to retrieve only the Day, Month, and Year Data in Queries.

When you have fields in a table that contain dates, you can use a Date method to retrieve only the year, month, or day instead of including the date in the resulting dataset of a query.

This is relatively simple and more efficient, and will increases the performance of Business Central.

To set up a Date Method on a query column, set the Method Property to Day, Month, and Year.

Property Value of Method Property:

Let’s go into some more details:

Year Method

The Year method gets the year from the date expression of a field value in the query column. The year is returned as an integer. If the year in the date expression is 0, then 1900 is returned.

The Result:

Month method

The Month method retrieves the month from the date expression of a field value in the query column. The month is returned as an integer, in the range of 1 to 12, where 1 represents January and 12 represents December. If the month in the date expression is 0, then 1 is returned.

Note: Same months in different years will be totaled.

For example, the Sales Quantity of 1896-S in February is -30.

This includes data for February 2020 and February 2021.

Day Method

The Day method retrieves the day from the date expression of a field value in the query column. The day is returned as an integer, in the range of 1 to 31, which represents the day of the month. If the day in the date expression is 0, then 1 is returned.

Note: The same as Month method, same day in different years and months will be totaled.

Group by Year-Month

Let’s return to the question, how to use the Date Method to group by Year-Month?
The hierarchy of the dataitem is important in Queries because it will determine the sequence in which data items are linked, which in turn will control the results.

So you can add another dataItem and link them with Posting Date. Set Method=year in the upper level and Method=Month in the lower level.

For example:

Source Code: for reference only

query 50102 ItemLedgerEntriesGroupByMonth
{
    QueryType = Normal;
    Caption = 'Item Ledger Entries Group By Month';
    QueryCategory = 'Item List';

    elements
    {
        dataitem(Item_Ledger_Entry; "Item Ledger Entry")
        {
            DataItemTableFilter = "Entry Type" = const(1), "Source Type" = const(1);//Entry Type::Sales, Source Type::Customer
            column(Item_No_; "Item No.")
            {
                Caption = 'Item No.';
            }
            column(Posting_Date_Year; "Posting Date")
            {
                Caption = 'Year';
                Method = Year;
            }
            dataitem(Item_Ledger_Entry_Month; "Item Ledger Entry")
            {
                DataItemLink = "Item No." = Item_Ledger_Entry."Item No.", "Posting Date" = Item_Ledger_Entry."Posting Date";
                DataItemTableFilter = "Entry Type" = const(1), "Source Type" = const(1);//Entry Type::Sales, Source Type::Customer
                column(Posting_Date_Month; "Posting Date")
                {
                    Caption = 'Month';
                    Method = Month;
                }
                column(Quantity; Quantity)
                {
                    Caption = 'Sales Quantity';
                    Method = Sum;
                }
                column(Sales_Amount__Actual_; "Sales Amount (Actual)")
                {
                    Caption = 'Sales Amount (Actual)';
                    Method = Sum;
                }
            }
        }
    }
}

We can use Excel to do a data check.

PS:
1. You can only use the date methods on fields of the Date and DateTime data type.

2. I have created three simple examples that I hope will help: GitHub | DataGroupByMonth

Test Video:

Find out more about Retrieving Date Data in Queries from Microsoft Docs.

Other information about Queries:
QueryCategory property – Smartlist? (Execute Queries from Pages)

Using the query as a data source for a page (Query.Open Method)

END

Hope this will help.

Thanks for reading.

ZHU