Business Central 2025 wave 2 (BC27): Date methods behavior on blank Date and DataTime fields when retrieving date data in queries

Hi, Readers.
Today I would like to talk about Date methods behavior on blank Date and DataTime fields when retrieving date data in queries. This is an update point starting with Business Central 2025 wave 2 (BC27).

As you might know, we can use the Date Method to retrieve only the Day, Month, and Year Data in Queries.

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

Property Value of Method Property:

More details: Using the Date Method to retrieve only the Day, Month, and Year Data in Queries [Group by Year-month]

However, the values that date methods return for blank fields differ by product version. In Business Central, a blank date is represented internally as 0D. In SQL Server, a blank date is 01-01-1753 (day-month-year). More details: Date methods behavior on blank Date and DataTime fields

The following table shows the version-specific results:

A simple example:

In BC27.0:

Test code:

query 50100 "ZY Purchase Order Query"
{
    Caption = 'ZY Purchase Order Query';
    OrderBy = Descending(Buy_from_Vendor_No_);
    UsageCategory = ReportsAndAnalysis;

    elements
    {
        dataitem(Purchase_Header; "Purchase Header")
        {
            column(Buy_from_Vendor_No_; "Buy-from Vendor No.")
            {
            }
            column(Buy_from_Vendor_Name; "Buy-from Vendor Name")
            {
            }
            column(Order_Date; "Order Date")
            {
            }
            column(Order_Date_Year; "Order Date")
            {
                Method = Year;
            }
            column(Order_Date_Month; "Order Date")
            {
                Method = Month;
            }
            column(Order_Date_Day; "Order Date")
            {
                Method = Day;
            }
            column(Currency_Code; "Currency Code")
            {
            }
            column(Amount_Including_VAT; "Amount Including VAT")
            {
            }
            dataitem(Purchase_Line; "Purchase Line")
            {
                DataItemLink = "Document Type" = Purchase_Header."Document Type",
                "Document No." = Purchase_Header."No.";
                column(No_; "No.")
                {
                }
                column(Description; Description)
                {
                }
                column(Quantity; Quantity)
                {
                }
                column(Amount; Amount)
                {
                }
                dataitem(Item; Item)
                {
                    DataItemLink = "No." = Purchase_Line."No.";
                    column(Inventory; Inventory)
                    {
                    }
                }
            }
        }
    }
}

END

Hope this will help.

Thanks for reading.

ZHU