DateTime in AL in kinda weird

Have you ever had a DateTime field or variable behaving, well, weird? Then this video might be for you. Check out the video:

https://youtu.be/3CTb-szzqRM

In this video, Eric dives into the quirks of working with DateTime fields in AL and Business Central. He demonstrates how DateTime values are always displayed in your local time zone but stored in UTC in the database, explores the Type Helper codeunit’s offset functions, and explains how this behavior can lead to confusing bugs — especially when code runs via web services in UTC context.

Setting Up a DateTime Field

To explore DateTime behavior, Eric starts by adding a simple DateTime field to the Customer table via a table extension:

tableextension 50100 "Customer" extends Customer
{
    fields
    {
        field(50100; TestDT; DateTime)
        {
            Caption = 'Test DateTime';
        }
    }
}

He then assigns CurrentDateTime() to this field on the first customer record and displays it on screen. The result is straightforward — you see the current date and time in your local time zone. For Eric, that’s Pacific Standard Time (UTC-8), so at approximately 11:53 AM local time, the value looks exactly as expected.

What’s Actually Stored in the Database

Here’s where things get interesting. When you look at the raw data in SQL Server, Business Central stores DateTime values in UTC — not in your local time zone. Extension fields live in a separate $extension table, and when Eric queries it, the stored time shows 19:53 instead of 11:53.

The math checks out: 11:53 (Pacific) + 8 hours = 19:53 (UTC). This is a critical detail to understand — the database always holds UTC, but AL always presents values in the session’s time zone.

The Automatic Time Zone Conversion in AL

When you work with DateTime values in AL — whether reading from the database, formatting, or displaying in a message — the platform automatically converts to and from the user’s time zone. Eric demonstrates this by using the Format() function on the DateTime field, which produces the same local time he sees on the UI.

He also shows that changing his user time zone (e.g., switching to Mountain Standard Time) shifts the displayed time accordingly, confirming that AL always operates under the session’s configured time zone.

The Type Helper Codeunit

Codeunit 10, Type Helper, is Microsoft’s Swiss Army knife for type conversions between .NET types and AL types. It contains several DateTime-related functions, though some of them behave unexpectedly.

GetCurrUTCDateTime — A Function That Doesn’t Quite Work

One would expect TypeHelper.GetCurrUTCDateTime() to return the current UTC time. However, because it returns a DateTime value, AL’s automatic time zone conversion kicks in and converts it right back to local time. The result? It shows the same value as CurrentDateTime().

Microsoft apparently recognized this issue, which is why they added alternative functions that return text instead:

  • GetCurrUTCDateTimeAsText — Returns the UTC time as a text value, bypassing the automatic conversion
  • GetCurrUTCDateTimeISO8601 — Returns the UTC time in ISO 8601 format, which is useful for web requests and JavaScript interop

Time Zone Offset Functions

The Type Helper codeunit also provides three functions for retrieving time zone offsets, each returning a Duration value:

var
    TypeHelper: Codeunit "Type Helper";
    Offset1, Offset2, Offset3 : Duration;
begin
    TypeHelper.GetTimezoneOffset(Offset1, 'Pacific Standard Time');
    TypeHelper.GetUserTimezoneOffset(Offset2);
    TypeHelper.GetUserClientTypeOffset(Offset3);
end;
  • GetTimezoneOffset — Takes a time zone ID string (e.g., 'Pacific Standard Time') and returns that zone’s offset from UTC
  • GetUserTimezoneOffset — Returns the current user’s configured time zone offset
  • GetUserClientTypeOffset — Returns the offset based on the user’s client type

The time zone ID values come from the Time Zone table in Business Central and match Windows time zone identifiers.

The Double-Offset Trap

This is where developers most commonly get burned. Eric demonstrates by subtracting the time zone offset from CurrentDateTime(), thinking he’ll produce a UTC value:

DT := CurrentDateTime();
DT -= Offset1;
Rec.TestDT := DT;
Rec.Modify();

When he checks SQL Server, the stored time is now a full day ahead. Why? Because the subtraction happens in local time zone context, producing a value that looks like UTC to AL. But when Business Central writes it to the database, it subtracts the offset again — resulting in a double offset (16 hours off instead of 0).

The fix is to add the offset back before writing to the database, effectively canceling out the automatic conversion:

DT := CurrentDateTime();
DT -= Offset1;
Message('Offset''ed time = %1', DT);

Rec.FindFirst();
Rec.TestDT := DT + Offset1;  // Re-add offset before database write
Rec.Modify();

After applying this correction, the SQL Server value is correct again. The key lesson: you are always working in your local time zone in AL, and you must account for the automatic conversion whenever you manipulate offsets manually.

The Web Service Problem

Things get even more complicated when code runs without a user session — such as when triggered by a web service call. In that context, the session operates in UTC, meaning:

  • CurrentDateTime() returns UTC
  • Today returns the UTC date, not the local date
  • No automatic time zone conversion occurs

This can cause real business problems. For example, if you’re in a time zone far from UTC (like Pacific, or anywhere in Asia), a web service call that arrives late in the evening might record the next day’s date in UTC. For accounting purposes, transactions should be recorded on the date where the company operates, not the UTC date.

Eric mentions that in some of their apps, they’ve added a setup field that allows users to specify that web service calls should apply a time zone offset. This ensures that posting dates and other date-sensitive values align with the company’s local business day rather than UTC.

Full Source Code

Here’s the complete page extension Eric built during the video, showing all the DateTime experiments in one place:

namespace DefaultPublisher.DateTime;

using Microsoft.Sales.Customer;
using System.Reflection;

pageextension 50100 CustomerListExt extends "Customer List"
{
    trigger OnOpenPage();
    var
        TypeHelper: Codeunit "Type Helper";
        Offset1, Offset2, Offset3 : Duration;
        DT: DateTime;
    begin
        TypeHelper.GetTimezoneOffset(Offset1, 'Pacific Standard Time');
        TypeHelper.GetUserTimezoneOffset(Offset2);
        TypeHelper.GetUserClientTypeOffset(Offset3);

        DT := CurrentDateTime();
        DT -= Offset1;
        Message('Offset''ed time = %1', DT);

        Rec.FindFirst();
        Rec.TestDT := DT + Offset1;  // Compensate for the automatic UTC conversion
        Rec.Modify();
    end;
}

Key Takeaways

  1. DateTime values in the database are always UTC — Business Central automatically converts to/from the user’s time zone when reading and writing.
  2. AL always works in the session’s time zone — Every DateTime value you see or manipulate in AL code has already been converted from UTC.
  3. Beware the double-offset trap — If you manually subtract a time zone offset and then write to the database, the platform will subtract it again. You must compensate by adding it back.
  4. Some Type Helper functions don’t work as expected — GetCurrUTCDateTime() returns a DateTime that gets auto-converted back to local time. Use the text-returning variants instead.
  5. Web services run in UTC — Code triggered by web services has no user session time zone, which can cause date mismatches for companies far from UTC. Consider adding a configurable offset for these scenarios.
  6. Time zones are inherently complex — Daylight saving time changes happen on different dates across regions, adding another layer of difficulty. Test thoroughly across time zones and execution contexts.
Picture of hougaard

hougaard