Instants, local times, dates and times of day

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Create.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_SQLSERVER")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_SQLSERVER environment variable to your SQL Server connection string.");

await new RealCase3_DatesAndTimes().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Create.RealCases;

/// <summary>
/// Dates and times, in four scenarios: an order placed in Manila written three ways (the same
/// instant, stored in UTC), calendar values (a birthday, an opening time, a delivery window, a
/// local event time), and what is refused: default(DateTime), and a date given as a string SQL Server can't read.
/// </summary>
public sealed class RealCase3_DatesAndTimes : ISample
{
    public string Name => "create-real-3-dates-and-times";

    public string Title => "Instants, local times, dates and times of day";

    public async Task RunAsync(SampleContext dbContext)
    {
        // The customer the orders and the contact belong to.
        var account = new ArxEntity("account");
        account["name"] = "Tailspin Toys";
        var accountId = await dbContext.Service.CreateAsync(account);

        await Scenario1_TheSameInstantThreeWaysAsync(dbContext, accountId);

        await Scenario2_CalendarValuesAsync(dbContext, accountId);

        await Scenario3_DefaultDateTimeIsRefusedAsync(dbContext, accountId);

        await Scenario4_ADateGivenAsAStringIsRefusedAsync(dbContext);
    }

    /// <summary>1. An instant goes into datetimeoffset. These three orders were placed at the same moment, written three ways.</summary>
    private static async Task Scenario1_TheSameInstantThreeWaysAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. The same instant written three ways into sales_order.ordered_on (datetimeoffset)");

        var fromOffset = new ArxEntity("sales_order");
        fromOffset["order_number"] = "SO-1001";
        fromOffset["account_id"] = new ArxEntityReference("account", accountId);
        fromOffset["ordered_on"] = new DateTimeOffset(2026, 11, 7, 18, 0, 0, TimeSpan.FromHours(8));            // Manila time

        var fromUtc = new ArxEntity("sales_order");
        fromUtc["order_number"] = "SO-1002";
        fromUtc["account_id"] = new ArxEntityReference("account", accountId);
        fromUtc["ordered_on"] = new DateTime(2026, 11, 7, 10, 0, 0, DateTimeKind.Utc);

        var fromLocal = new ArxEntity("sales_order");
        fromLocal["order_number"] = "SO-1003";
        fromLocal["account_id"] = new ArxEntityReference("account", accountId);
        fromLocal["ordered_on"] = new DateTime(2026, 11, 7, 10, 0, 0, DateTimeKind.Utc).ToLocalTime();          // ARX converts Local to UTC

        var orders = new List<ArxEntity> { fromOffset, fromUtc, fromLocal };
        var ids = await dbContext.Service.CreateMultipleAsync(orders);

        for (var i = 0; i < orders.Count; i++)
        {
            var stored = await dbContext.Direct.TextRowAsync("sales_order", ids[i].AsInt64(), "ordered_on");
            Show($"{orders[i]["order_number"]}: stored {stored["ordered_on"]}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT order_number, ordered_on, SWITCHOFFSET(ordered_on, '+00:00') AS in_utc
         * FROM sales_order
         * WHERE order_number IN ('SO-1001', 'SO-1002', 'SO-1003')
         * ORDER BY order_number;
         * -- Expect: 3 rows, ordered_on and in_utc all 2026-11-07 10:00:00 +00:00: a DateTimeOffset goes in as its
         * -- UTC instant (18:00 +08:00 is 10:00 UTC), so the same value would be right in a datetime2 column too.
         * ===========================================================
         */
    }

    /// <summary>2. Calendar values have their own types. A wall-clock time with no zone (an Unspecified DateTime) goes into datetime2.</summary>
    private static async Task Scenario2_CalendarValuesAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. A birthday, an opening time, a delivery window and a local event time");

        var contact = new ArxEntity("contact");
        contact["account_id"] = new ArxEntityReference("account", accountId);
        contact["first_name"] = "Maria";
        contact["last_name"] = "Santos";
        contact["birthday"] = new DateOnly(1990, 2, 28);
        var contactId = await dbContext.Service.CreateAsync(contact);

        var schedule = new ArxEntity("data_type_showcase");
        schedule["time_value"] = new TimeOnly(9, 0);
        schedule["datetime2_value"] = new DateTime(2026, 12, 24, 18, 0, 0, DateTimeKind.Unspecified);
        var scheduleId = await dbContext.Service.CreateAsync(schedule);

        var times = await dbContext.Direct.TextRowAsync("data_type_showcase", scheduleId.AsInt64(), "time_value", "datetime2_value");
        Show($"birthday {(await dbContext.Direct.TextRowAsync("contact", contactId.AsInt64(), "birthday"))["birthday"]}, opens {times["time_value"]}, "
            + $"event {times["datetime2_value"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, birthday
         * FROM contact
         * WHERE first_name = 'Maria' AND last_name = 'Santos';
         *
         * SELECT time_value, datetime2_value
         * FROM data_type_showcase
         * WHERE datetime2_value = '2026-12-24 18:00:00';
         * -- Expect: birthday 1990-02-28; 09:00:00, 2026-12-24 18:00:00.
         * ===========================================================
         */
    }

    /// <summary>3. default(DateTime) means "not set", and SQL Server would store 0001-01-01: refused before anything is sent.</summary>
    private static async Task Scenario3_DefaultDateTimeIsRefusedAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. default(DateTime) is refused: use null for \"no value\"");

        var unset = new ArxEntity("sales_order");
        unset["order_number"] = "SO-1004";
        unset["account_id"] = new ArxEntityReference("account", accountId);
        unset["ordered_on"] = default(DateTime);

        try
        {
            await dbContext.Service.CreateAsync(unset);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.InvalidDateTimeValue)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM sales_order
         * WHERE order_number = 'SO-1004';
         * -- Expect: 0: default(DateTime) was refused before anything was sent.
         * ===========================================================
         */
    }

    /// <summary>
    /// 4. ARX never parses a string as a date, but SQL Server converts one it can read, by the session's date format
    /// (month first under the default us_english: 01/07/1992 would be stored as January 7). This day-first date can't be
    /// read, so it is refused. Parse dates yourself, with the format you mean.
    /// </summary>
    private static async Task Scenario4_ADateGivenAsAStringIsRefusedAsync(SampleContext dbContext)
    {
        Step("4. A day-first date given as a string is refused; DateOnly.ParseExact works");

        var fromCsv = new ArxEntity("contact");
        fromCsv["first_name"] = "Leo";
        fromCsv["last_name"] = "Park";
        fromCsv["birthday"] = "13/07/1992";

        try
        {
            await dbContext.Service.CreateAsync(fromCsv);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.InvalidValueFormat)
        {
            ShowError(error);
        }

        fromCsv["birthday"] = DateOnly.ParseExact("13/07/1992", "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
        var id = await dbContext.Service.CreateAsync(fromCsv);
        Show($"stored contact {id}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT first_name, last_name, birthday
         * FROM contact
         * WHERE first_name = 'Leo' AND last_name = 'Park';
         * -- Expect: 1 row, birthday 1992-07-13 (the second attempt, with DateOnly.ParseExact).
         * ===========================================================
         */
    }
}

  > 1. The same instant written three ways into sales_order.ordered_on (datetimeoffset)
      SO-1001: stored 2026-11-07T10:00:00+00:00
      SO-1002: stored 2026-11-07T10:00:00+00:00
      SO-1003: stored 2026-11-07T10:00:00+00:00
  > 2. A birthday, an opening time, a delivery window and a local event time
      birthday 1990-02-28, opens 09:00:00, event 2026-12-24T18:00:00
  > 3. default(DateTime) is refused: use null for "no value"
      caught ArxValidationException: ARX1013 InvalidDateTimeValue: Attribute 'ordered_on' on 'sales_order' is default(DateTime) (DateTime.MinValue), which means "not set"; SQL Server would store it as 0001-01-01. Set a real value or null.
  > 4. A day-first date given as a string is refused; DateOnly.ParseExact works
      caught ArxDatabaseException: ARX4003 InvalidValueFormat: Create on 'contact' has a value in a format its column can't accept.
      stored contact 3