Test assertions on DateTime values

Recently, I was running a large test suite to verify my changes in the Base Application item journal posting functionality. Changes were quite extensive and affecting the sensitive core of the inventory posting routine, so I expected some test cases to fail on the first iteration. What I did not expect, though, was the number of failures completely unrelated to my modifications, and especially one error message standing out from all the false positive results lined up in the test suite.

The message read:

Starting Date-Time must be equal to '01/26/25 10:00 AM' in Requisition Line: Worksheet Template Name=PLANNING, Journal Batch Name=DEFAULT, Line No.=10000. Current value is '01/26/25 10:00 AM'

The message looks pretty weird - the expected and actual values I emphasized in bold in the error message above are actually the same, which raises a question about the reason of the failure.

The error is triggered by the TestField function in this test verification:

[Test]
procedure RequisitionLineStartingEndingDateTimeUpdatedFromRouting()
var
    RequisitionLine: Record "Requisition Line";
    PlanningRoutingLine: Record "Planning Routing Line";
    RequisitionWkshName: Record "Requisition Wksh. Name";
    PlanningLineManagement: Codeunit "Planning Line Management";
begin
    LibraryPlanning.SelectRequisitionWkshName(
        RequisitionWkshName,
        RequisitionWkshName."Template Type"::Planning);
    LibraryPlanning.CreateRequisitionLine(
        RequisitionLine,
        RequisitionWkshName."Worksheet Template Name",
        RequisitionWkshName.Name);
    MockPlanningRoutingLine(PlanningRoutingLine, RequisitionLine);
        
    PlanningLineManagement.CalculatePlanningLineDates(RequisitionLine);

    RequisitionLine.TestField(
        "Starting Date-Time",
        CreateDateTime(
            PlanningRoutingLine."Starting Date", 
            PlanningRoutingLine."Starting Time"));
    
    RequisitionLine.TestField(
          "Ending Date-Time",
          CreateDateTime(
              PlanningRoutingLine."Ending Date",
              PlanningRoutingLine."Ending Time"));
end;

The bold line in this code snippet is the one that causes the error. If we dig into this code with the debugger, we can see that it does not do much more than reassigning the date and time values from the planning routing line to the requisition line. It can be simplified and demonstrated on the following example.

[Test]
procedure DateTimeTestField()
var
    RequisitionLine: Record "Requisition Line";
    StartingDateTime: DateTime;
begin
    StartingDateTime := CreateDateTime(20250126D, 100011.075T);
    RequisitionLine."Starting Date-Time" := StartingDateTime
    RequisitionLine.Insert();

    RequisitionLine.Find();
    RequisitionLine.TestField("Starting Date-Time", StartingDateTime);
end;

Here, I simply create a datetime value in a local variable and assign it to a table field that is saved to the database. No manipulations with the field value, no magic tricks - I just save and re-read it to compare with the DateTime variable which presumably contains the same value. And that's where the TestField fails - which hints that the assumption is probably wrong and the actual value stored in the database differs from the value assigned to the local DateTime variable.

Let's appeal to the debugger to verify the assumption.

The DateTime variable in the test function keeps the value assigned to it in the first line of the test - 10:00:11 and 75 milliseconds.

But what about the other side of the equation?

Quite unexpectedly, the same value simply saved to the database and retrieved again, has changed to 10:00:11.077, and the 2 milliseconds difference caused the test failure. The reason of this difference is explained in a Microsoft Learn article: https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16

Unlike Business Central, SQL Server does not store DateTime values precisely to the millisecond. Stored values are rounded to the nearest increment of .000, .003, or .007 seconds, as illustrated in the Microsoft documentation article. Perfectly in line with the documentation, 75 milliseconds, once saved and re-queried, turn into 77 milliseconds without any other data changes.

The following table shows the DateTime value transformation from a BC variable (left-side column) to a respective SQL Server value (right-side column).

BC DateTime value	SQL DateTime Value
10:00:11.070 10:00:11.071	10:00:11.070
10:00:11.072 10:00:11.073 10:00:11.074	10:00:11.073
10:00:11.075 10:00:11.076 10:00:11.077 10:00:11.078	10:00:11.077
10:00:11.079	10:00:11.080

One of the possible solutions to this issue in test codeunits (and the easiest one) is to stop using the TestField method in test verifications in favour of the verification methods Assert.AreEqual, LibraryAssert.AreEqual, or LibraryAssert.AreEqualDateTime. TestField is generally not recommended for test verifications, so let's see what benefits can specialized assertion methods bring in, and which one to use.

Before talking about a specific method, we need to decide which assertion library to use. Currently, Business Central offers two libraries: Assert and Library Assert. Although both libraries are supported, Library Assert is a newer version of the Assert codeunit and is preferred for new development. Besides, if you do any development for the System Application, this is the only choice because the Assert codeunit is implemented on a higher level and available only in the Base Application and its extensions.

Method AreEqual is available in both libraries and uses the Format function for non-numeric parameters to compare values - so it actually compares strings, a text representation of date and time. Non-numeric values are transformed to text with the help of the Format function:

exit((TypeOf(Left) = TypeOf(Right)) and (Format(Left, 0, 2) = Format(Right, 0, 2)))

With the format number 2, Format ignores the fractional part of the date/time, so in my example described in the beginning of the article, the result is simply truncated to 01/26/25 10:00, without milliseconds, before the comparison operation.

So replacing the TestField with the AreEqual function solves the issue:

Assert.AreEqual(
    RequisitionLine."Starting Date-Time", StartingDateTime,
    'DateTime values must match.');

With this verification, the test passes, because the difference of 2 milliseconds is simply ignored. And this should suffice in most of the testing scenarios - after all, nobody builds a requisition plan with the millisecond precision.

But what if your specific test case requires this level of detail? Then the method AreEqualDateTime from the Library Assert can be useful. If I replace the same verification line with the call to AreEqualDateTime, the test is going to fail.

LibraryAssert.AreEqualDateTime(
    RequisitionLine."Starting Date-Time", StartingDateTime,
    'DateTime values must match.');

This is the error message that I receive from the new verification:

Now the test is failing again, but the error message contains all the details. It's much less puzzling that the one triggered by TestField, which claims that two equal values are in fact different.

But keep in mind that if you want to use the AreEqualDateTime method, direct comparison between an AL DateTime variable and a value retrieved from the database can sometimes yield unexpected results. Rounding has to to be handled by the test app in this case.