LC0029
When working with DateTime and Time variables, it's crucial to understand that these values are subject to rounding by SQL Server. This can lead to unexpected issues when comparing these values against their original counterparts. A great blog post describing this issue in depth is available on Times in BC – you didn’t know that! – The BC Docs Librarian
To prevent inaccuracies and avoid the pitfalls of comparing DateTime and Time values, it is highly recommended to use the CompareDateTime method provided by the Type Helper codeunit. This method ensures that comparisons are made correctly, accounting for the SQL Server's rounding behavior.
// Direct comparing
if (FirstRecord."DateTimeField" <> SecondRecord."DateTimeField") then
// Do Magic if DateTime Fields are different
// Comparing with CompareDateTime method
if (TypeHelper.CompareDateTime(FirstRecord."DateTimeField", SecondRecord."DateTimeField") <> 0) then
// Do Magic if DateTime Fields are differentMaking use of the CompareDateTime method is a temporary solution.
we are planning to move to a higher precision datetime representation
In the undefined future the use of the CompareDateTime method will no longer be needed. Maybe then it would even be recommended to not use this method to prevent inaccurate comparisations.
codeunit 50000 "Insert DateTime"
{
trigger OnRun()
var
Customer: Record Customer;
NewLastModifiedDateTime: DateTime;
begin
Customer.Get('10000');
Evaluate(NewLastModifiedDateTime, '2022-05-24T12:40:22.468Z', 9);
Customer."Last Modified Date Time" := NewLastModifiedDateTime;
Customer.Modify();
Message('After Modify %1 = %2', Format(Customer."Last Modified Date Time", 0, 9), '2022-05-24T12:40:22.468Z');
Commit();
Customer.Get('10000');
Message('After Second Get %1 <> %2', Format(Customer."Last Modified Date Time", 0, 9), '2022-05-24T12:40:22.468Z');
end;
}The code will output the following Messages:
| After Modify | 2022-05-24T12:40:22.468Z | = | 2022-05-24T12:40:22.468Z |
| After Second Get | 2022-05-24T12:40:22.467Z | <> | 2022-05-24T12:40:22.468Z |
The core of the problem lies in the behavior of SQL Server, which rounds DateTime and Time values. This means that a DateTime or Time value can undergo changes once it's written to the database. Consequently, comparing this altered value with the original one can result in erroneous outcomes. SQL DateTime values are rounded to increments of .000, .003, or .007 seconds.
| Time value | stored in SQL as |
|---|---|
| 23:59:59.990 | 23:59:59.990 |
| 23:59:59.991 | 23:59:59.990 |
| 23:59:59.992 | 23:59:59.993 |
| 23:59:59.993 | 23:59:59.993 |
| 23:59:59.994 | 23:59:59.993 |
| 23:59:59.995 | 23:59:59.997 |
| 23:59:59.996 | 23:59:59.997 |
| 23:59:59.997 | 23:59:59.997 |
| 23:59:59.998 | 23:59:59.997 |
| 23:59:59.999 | 00:00:00.000 |
More details on Microsoft Learn here for insights into how SQL Server handles these data types.