[BC Idea]: New Compare Methods for Time and DateTime Comparison

[JohnnyUndercover]

BC Idea Link

https://experience.dynamics.com/ideas/idea/?ideaid=59c5f0fb-0683-ee11-a81c-000d3adc4046

Description

🕮 Describe the issue
The comparison of time values does not work if you compare the time value before writing to the database with the time value that was written to the database.
The cause of the problem is that in BC DateTime and Time values are rounded by SQL Server (See MS Docs). This means that a DateTime / Time value can change just because it was written to the database, the comparison with the original value then runs on error.

For DateTimes there is a method "CompareDateTime" in the codeunit TypeHelper to compare DateTimes with a small threshold value to compensate for the rounding of the DateTime values. However, the threshold seems to be unnecessarily large.
Also, there is no method to compare Times.

🔧 To Reproduce

Run the follwowing code:

codeunit 50100 "Insert Time"
{
    trigger OnRun()
    var
        Contact: Record Contact;
        NewLastModifiedTime: Time;
        TimeText: Text;
    begin
        TimeText := '12:40:22.468Z';
        Contact.Get('KT100006');
        Evaluate(NewLastModifiedTime, TimeText, 9);
        Contact."Last Time Modified" := NewLastModifiedTime;
        Contact.Modify();

       Message('Before Write to Database %1 = %2', Format(Contact."Last Time Modified", 0, 9), Format(NewLastModifiedTime, 0, 9));

        Commit();
        Contact.Get('KT100006');
        Message('After Write to Database %1 <> %2', Format(Contact."Last Time Modified", 0, 9), Format(NewLastModifiedTime, 0, 9));
    end;
}

The code will output the following Messages:

Before Write to Database 12:40:22.468Z = 12:40:22.468Z

After Write to Database 12:40:22.467Z <> 12:40:22.468Z

📣 Expected behavior
There should be methods to compare DateTime / Time values that take care of the sql rounding.

Additional context
Previous Disscussion to this topic: https://github.com/microsoft/BusinessCentralApps/issues/483
Yammer Thread: https://www.yammer.com/dynamicsnavdev/#/threads/show?threadId=2506817338753024

[JohnnyUndercover]

@JesperSchulz i created a pr draft, if you could approve the issue i would make it into a real pr.

[JesperSchulz]

I am just waiting for a reply from our platform team, if this is the right way to go. If they agree, I'll approve this one instantly! 😊

[JesperSchulz]

Discussions are still ongoing. I'll soon post an update!

[JesperSchulz]

I've received feedback from our platform team, and they have replied: "Since we are planning to move to a higher precision datetime representation (datetime2), then this PR would soon be obsolete".

Based on that statement, I would recommend not to spend any more time on this idea. Your work has helped put focus on this issue and has raised the priority for a "real" solution, which almost is better than providing a workaround for the issue yourself (I'd argue) 😉

In the end, it is up to you if you'd like to invest the work to get this shipped while we wait. But chances are it will hardly see the light of day, before it is obsolete. Let me know what you think!

(NB: as always, we cannot make any guarantees when it comes to the release date of such change.)

[NKarolak]

Aimed for BC 24 :-)
https://x.com/KennieNP/status/1730162200612933716?s=20

[JohnnyUndercover]

I fully agree. A fix at platform level is the best solution here.