Controlling DateTime Conversion in AL Code

When working with Excel and converting DateTime values in AL (Application Language) code, the date might be converted automatically through an API call or other methods. To prevent this conversion from happening, you can control the behavior by setting a flag that determines whether the date should be interpreted as UTC or Local time.

Source: IP Location - Time Zones

Conversion of DateTime values

In the AL code provided, the conversion of DateTime values from Excel (as OLE Automation dates) depends on the value of a flag called ReadDateTimeInUtcDate.

• UTC Time: If you want the DateTime to be read as is (without conversion), you need to set the flag as true.
• Local Time: If you want the DateTime to be handled by converting to UTC, set the flag to false.

The ConvertDateTimeDecimalToDateTime function in table 370 "Excel Buffer" is responsible for converting a DateTime value from an OLE Automation date (used by Excel) to the appropriate DateTime format. The behavior of this conversion is controlled by the ReadDateTimeInUtcDate flag.

procedure ConvertDateTimeDecimalToDateTime(DateTimeAsOADate: Decimal): DateTime
var
    DotNetDateTime: DotNet DateTime;
    DateTimeResult: DateTime;
begin
    DotNetDateTime := DotNetDateTime.FromOADate(DateTimeAsOADate);
    // Use the ReadDateTimeInUtcDate flag to determine whether to use UTC or Local time
    if ReadDateTimeInUtcDate then
        Evaluate(DateTimeResult, DotNetDateTime.ToString()) // Convert to UTC
    else
        DateTimeResult := DotNetDateTime.DateTime(DotNetDateTime.Ticks, DotNetDateTimeKind.Local); // Convert to Local Time
    exit(DateTimeResult);
end;

Eliminate the conversion

To eliminate the conversion AL provides the following SetReadDateTimeInUtcDate function in the same table.

procedure SetReadDateTimeInUtcDate(NewValue: Boolean)
begin
    ReadDateTimeInUtcDate := NewValue;
end;

Example: Setting the Flag

To set the flag, use the SetReadDateTimeInUtcDate procedure:

procedure importFromExcel()
    var
        SheetArray: Record "Name/Value Buffer" temporary;
    begin
        TempExcelBuffer.Reset();
        TempExcelBuffer.DeleteAll();
        TempExcelBuffer.SetReadDateTimeInUtcDate(true); // read as is
    end;