Category: Dataverse

SQL4CDS UTC Mode: Converting Advanced Find Date Filters to UTC Boundaries– A Quick Reference (Dataverse / Dynamics 365)


While validating record counts for a Work Orders view in Dynamics 365 Field Service, we ran into the same underlying issue covered in an earlier post – Why SQL4CDS Record Counts May Not Match Advanced Find for Date Filters.

This time, instead of just explaining the root cause again, we put together a quick reference table for converting an Advanced Find date range into the correct SQL4CDS UTC boundary, for any user time zone.

The Scenario

Advanced Find query on Work Orders, filtered on Created On:

  • On or After 01/01/2026
  • On or Before 05/01/2026

The user running this is in Auckland, New Zealand. (User’s Time Zone is Auckland)

We were running SQL4CDS in UTC mode. A query that simply matches the literal date strings against createdon will not reliably reproduce the Advanced Find count, because Advanced Find evaluates the date range in the user’s local time zone, while createdon is stored in UTC. The two only line up once the date range is converted to explicit UTC boundaries.

The Reliable Formula

StartBoundaryUTC = StartDate 00:00:00 (user’s local time) → converted to UTC

EndBoundaryUTC   = (EndDate + 1 day) 00:00:00 (user’s local time) → converted to UTC

WHERE createdon >= StartBoundaryUTC AND createdon < EndBoundaryUTC

Two points worth calling out:

  • The upper boundary always uses End Date + 1 day, with a strict <, not <=. This avoids any ambiguity around milliseconds and reliably captures the entire end date.
  • For time zones ahead of UTC (New Zealand, India), convert by subtracting the offset. For time zones behind UTC (Hawaii, US), convert by adding the offset.

Quick Reference Table

Boundary used below: 1/01/2026 to 5/01/2026 (end boundary = 6/01/2026 local, converted to UTC).

Time ZoneOffsetDST Active?Start CalculationEnd CalculationSQL4CDS Boundary
UTC+0:00No DST2026-01-01T00:00 − 0:002026-01-06T00:00 − 0:00>= ‘2026-01-01T00:00:00Z’ AND < ‘2026-01-06T00:00:00Z’
India (IST)+5:30No DST2026-01-01T00:00 − 5:302026-01-06T00:00 − 5:30>= ‘2025-12-31T18:30:00Z’ AND < ‘2026-01-05T18:30:00Z’
New Zealand (NZDT – summer)+13:00Yes (active in Jan)2026-01-01T00:00 − 13:002026-01-06T00:00 − 13:00>= ‘2025-12-31T11:00:00Z’ AND < ‘2026-01-05T11:00:00Z’
New Zealand (NZST – winter)+12:00Yes (inactive in Jan)2026-01-01T00:00 − 12:002026-01-06T00:00 − 12:00>= ‘2025-12-31T12:00:00Z’ AND < ‘2026-01-05T12:00:00Z’
Hawaii (HST)−10:00No DST2026-01-01T00:00 + 10:002026-01-06T00:00 + 10:00>= ‘2026-01-01T10:00:00Z’ AND < ‘2026-01-06T10:00:00Z’

Since January falls in NZ summer, the Auckland user’s boundary above uses NZDT (+13:00):

SELECT count(1)
FROM msdyn_workorder
WHERE createdon >= '2025-12-31T11:00:00Z'
AND createdon < '2026-01-05T11:00:00Z'

This reproduces the Advanced Find count for the same range.

NZ DST Transition Windows

New Zealand does not stay on a single offset year-round, so the correct value depends on the date range being queried, not the date the query is run.

PeriodOffset
Late Sep – early Apr (NZDT)UTC+13
Early Apr – late Sep (NZST)UTC+12

Confirm the exact transition dates for the specific year, as they shift slightly.

Rules of Thumb

RuleReason
End boundary = End Date + 1 day, use < not <=Captures the full end date without truncating time
Time zones ahead of UTC: subtract the offsetUTC = Local − Offset
Time zones behind UTC: add the offsetUTC = Local + Offset
Check DST for the query dates, not today’s dateThe same time zone can have two different offsets depending on the time of year

Key Takeaway

When running SQL4CDS in UTC mode, the reliable and repeatable approach is to convert the Advanced Find date range into explicit UTC boundaries using the local-time offset (accounting for DST where applicable), and query using >= / < against those boundaries.

Reference

For more background on how SQL4CDS interprets date and time values in UTC vs Local mode, see Mark Carrington’s article: Date/Time handling in SQL 4 CDS

Hope it helps..

Advertisements

When Entity.Id Is Guid.Empty with QueryExpression.Distinct = True (Dataverse)


While optimising the performance of a Dataverse plugin, we noticed a QueryExpression using ColumnSet(true). The business logic only required a few attributes, so replacing ColumnSet(true) with a minimal ColumnSet looked like an easy performance improvement. The query also used Distinct = true, which we left unchanged because it had always been there and everything was working correctly.

The Original Query

query.ColumnSet = new ColumnSet(true);
query.Distinct = true;

We changed the query to retrieve only the attributes required by the business logic:

query.ColumnSet = new ColumnSet(
    "msdyn_systemstatus",
    "msdyn_datewindowstart",
    "custom_cancelledreason");
query.Distinct = true;

The optimisation looked perfectly valid. The query returned the expected records, but part of the plugin logic suddenly stopped working.

The Unexpected Bug

The plugin compared Work Orders using Entity.Id to determine whether a record already existed in a collection. During debugging, we discovered that every retrieved entity had Guid.Empty as its Id, causing the comparison logic to fail and duplicate records to be added.

Finding the Root Cause

To isolate the problem, we reproduced the behaviour with a simple Lead query.

QueryExpression query = new QueryExpression("lead");
query.ColumnSet = new ColumnSet("lastname");
query.Distinct = true;

Once again, the record was returned successfully, but Entity.Id was Guid.Empty.

While reading the Microsoft documentation for QueryExpression.Distinct, we found the following remark:

“When the Distinct property is true, the results returned don’t include primary key values for each record because they represent an aggregation of all the distinct values.”

The Fix

Including the primary key in the ColumnSet resolved the issue.

query.ColumnSet = new ColumnSet("leadid", "lastname");
query.Distinct = true;

After this change, both Entity.Id and the leadid attribute were populated correctly.

One More Observation

While investigating the issue, we realised something else. The original query used ColumnSet(true) together with Distinct = true. Since ColumnSet(true) retrieves every readable attribute, including the primary key, every record is already unique because the primary key itself is unique. In that particular QueryExpression there were no LinkEntity joins or other scenarios that could naturally produce duplicate rows. That meant Distinct = true was not really providing any value. In fact, once we reviewed the query, removing Distinct = true was a cleaner solution than simply adding the primary key back into the ColumnSet.
This serves as a useful reminder that performance optimisation is not just about reducing the columns retrieved. It is also a good opportunity to question whether every part of the original query is still necessary.

Lessons Learned

• Replacing ColumnSet(true) with a minimal ColumnSet is a good optimisation.
• If a query uses Distinct = true and the code relies on Entity.Id, include the primary key in the ColumnSet.
• Review whether Distinct = true is actually required. In many QueryExpression scenarios, especially those without joins, it may be redundant.
• Small performance improvements can sometimes expose subtle behaviours that are easy to overlook.

Hope it helps..

Advertisements

Implementing Server-Side Honeypot Validation for Dynamics 365 Customer Insights – Journeys Forms


In our previous post, we implemented a simple Honeypot for Dynamics 365 Customer Insights – Journeys Real-Time Marketing forms using JavaScript.

Although that prevents most automated submissions, the validation only runs in the browser. Anyone can bypass the JavaScript and submit requests directly to the server.

Fortunately, Customer Insights – Journeys provides a server-side validation pipeline through the msdynmkt_validateformsubmission message, allowing us to validate every form submission before a Lead, Contact, or custom record is created.

When a form with the data-validate-submission attribute is submitted, Microsoft first executes its built-in validation plugin, followed by any custom plugins registered on the same message.

Update the HTML of the marketing form and add the attribute –


One important behavior to be aware of is that if our form doesn’t contain Microsoft’s built-in CAPTCHA fields, the default Microsoft validation plugin sets IsValid = false, causing the submission to fail. Our custom plugin must overwrite this response by returning IsValid = true when our validation succeeds, or IsValid = false if it fails.
In this post, we’ll use this pipeline to validate the Honeypot field that we created in the previous article.

Previous article:
Implementing a Simple Honeypot in Dynamics 365 Customer Insights – Journeys Real-Time Marketing Forms

Register the Validation Plugin

The submitted form values are available in the msdynmkt_formsubmissionrequest input parameter.

Next, as described in the Microsoft Learn documentation, we can overwrite the response by returning a new ValidateFormSubmissionResponse with IsValid = true.

Read the Honeypot Field

var request = Deserialize<FormSubmissionRequest>(                (string)context.InputParameters["msdynmkt_formsubmissionrequest"]);
 var fields = request?.Fields ?? new List<FormField>();
 string honeypotValue = fields.FirstOrDefault(f => f.Key == HoneypotFieldName)?.Value;

Reject Bot Submissions

if (!string.IsNullOrWhiteSpace(honeypotValue))
{
    SetValidationResponse(context, false, "Form validation failed.");
    return;
}

Return the Validation Response

Customer Insights expects the plugin to return a ValidateFormSubmissionResponse. We also include the Honeypot field in ValidationOnlyFields, so it isn’t mapped to the target record.

On Successful Form submission, we can see our validation plugin triggering

If server side validation fails (i.e. honey pot field has a value in it) we get our custom error message and submission fails.

Complete Plugin

using Microsoft.Xrm.Sdk;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Runtime.Serialization;
using System.Runtime.Serialization.Json;
using System.Text;

namespace DI.D365.Plugins.Marketing
{
    /// <summary>
    /// Plugin Registration
    ///
    /// Message: msdynmkt_validateformsubmission
    /// Stage: PostOperation
    /// Mode: Synchronous    
    /// </summary>
    public class ValidateHoneypot : IPlugin
    {
        private const string HoneypotFieldName = "hp_check";
        private const string ErrorMessage = "Form validation failed.";

        public void Execute(IServiceProvider serviceProvider)
        {
            var context = (IPluginExecutionContext)serviceProvider.GetService(
                typeof(IPluginExecutionContext)
            );

            var tracing = (ITracingService)serviceProvider.GetService(typeof(ITracingService));

            if (!context.InputParameters.Contains("msdynmkt_formsubmissionrequest"))
            {
                tracing.Trace("Form submission request not found.");
                return;
            }

            var request = Deserialize<FormSubmissionRequest>(
                (string)context.InputParameters["msdynmkt_formsubmissionrequest"]
            );

            var fields = request?.Fields ?? new List<FormField>();

            string honeypotValue = fields.FirstOrDefault(f => f.Key == HoneypotFieldName)?.Value;

            tracing.Trace($"Honeypot Value: {honeypotValue}");

            if (!string.IsNullOrWhiteSpace(honeypotValue))
            {
                tracing.Trace("Honeypot validation failed.");

                SetValidationResponse(context, false, ErrorMessage);

                return;
            }

            tracing.Trace("Honeypot validation passed.");

            SetValidationResponse(context, true, null);
        }

        private void SetValidationResponse(
            IPluginExecutionContext context,
            bool isValid,
            string error
        )
        {
            var response = new ValidateFormSubmissionResponse
            {
                IsValid = isValid,
                ValidationOnlyFields = new List<string> { HoneypotFieldName },
                Error = error
            };

            context.OutputParameters["msdynmkt_validationresponse"] = Serialize(response);
        }

        private static T Deserialize<T>(string json)
        {
            using (var stream = new MemoryStream(Encoding.UTF8.GetBytes(json)))
            {
                return (T)new DataContractJsonSerializer(typeof(T)).ReadObject(stream);
            }
        }

        private static string Serialize<T>(T value)
        {
            using (var stream = new MemoryStream())
            {
                new DataContractJsonSerializer(typeof(T)).WriteObject(stream, value);

                return Encoding.UTF8.GetString(stream.ToArray());
            }
        }
    }

    #region Helper Classes

    [DataContract]
    public class FormSubmissionRequest
    {
        [DataMember(Name = "Fields")]
        public List<FormField> Fields { get; set; }
    }

    [DataContract]
    public class FormField
    {
        [DataMember(Name = "Key")]
        public string Key { get; set; }

        [DataMember(Name = "Value")]
        public string Value { get; set; }
    }

    [DataContract]
    public class ValidateFormSubmissionResponse
    {
        [DataMember(Name = "IsValid")]
        public bool IsValid { get; set; }

        [DataMember(Name = "ValidationOnlyFields")]
        public List<string> ValidationOnlyFields { get; set; }

        [DataMember(Name = "Error")]
        public string Error { get; set; }
    }
    #endregion
}

Conclusion

Adding a client-side Honeypot is a great first step, but validating it on the server makes the solution much more robust. Since every submission passes through the  msdynmkt_validateformsubmission pipeline, bots can’t bypass the validation simply by skipping our JavaScript.

“The msdynmkt_validateformsubmission Custom API is implemented by Microsoft’s Microsoft.Dynamics.Cxp.Forms.Plugins.Plugins.ValidateFormSubmissionPlugin, which performs the default Microsoft CAPTCHA validation and initializes the msdynmkt_validationresponse. We can then register our own plugin steps on the same message. Microsoft recommends registering custom validation plugins with an Execution Order of 20, allowing them to execute after the out-of-the-box Microsoft.Dynamics.Cxp.FormsReCaptcha.Plugins.ReCaptchaValidationPlugin (Execution Order 10) and overwrite the validation response if required.”

References

Hope it helps..

Advertisements

Implementing a Simple Honeypot in Dynamics 365 Customer Insights – Journeys Real-time Marketing Forms


While working with Dynamics 365 Customer Insights – Journeys Real-time Marketing Forms, we wanted to explore a simple way of reducing bot submissions.

A common technique used on websites is a honeypot. The idea is straightforward:

  • Add a field that genuine users never see.
  • Hide it using JavaScript.
  • If the field contains a value when the form is submitted, assume it was completed by a bot and cancel the submission.

In this post, we’ll implement this using the supported Client-side Extensibility events provided by Dynamics 365.

Step 1: Add an Unbound Field

Add a new Short Text field to the form.

The field can be Unbound, as it is only used for validation and does not need to be stored in Dataverse.

We used hp_check for the field name as this avoids browser autofill heuristics that may populate well-known fields such as Middle Name, Email, or Phone.

Step 2: Hide the Field

The field should remain in the HTML so that automated bots can still discover it, but it should not be visible to genuine users.

In our implementation, the field is hidden using CSS, which avoids any brief flicker that could occur if it were hidden only after the form loads.

.textFormFieldBlock:has(input[name="hp_check"]) {
    position: absolute !important;
    left: -9999px !important;
    width: 1px !important;
    height: 1px !important;
    overflow: hidden !important;
}

Step 3: Validate During Form Submission

Using the supported d365mkt-afterformload and d365mkt-formsubmit events, configure the field and validate it before submission.

<script>
let honeypotField = null;

document.addEventListener("d365mkt-afterformload", function () {

    honeypotField = document.querySelector('[name="hp_check"]');

    if (!honeypotField) {
        return;
    }

    honeypotField.setAttribute("autocomplete", "off");
    honeypotField.setAttribute("tabindex", "-1");
    honeypotField.setAttribute("aria-hidden", "true");
});

document.addEventListener("d365mkt-formsubmit", function (event) {

    if (honeypotField && honeypotField.value.trim() !== "") {
        event.preventDefault();
    }

});
</script>

The d365mkt-formsubmit event is cancelable, so calling event.preventDefault() prevents the form from being submitted.

Testing

To test the implementation, we opened the browser Developer Tools and executed the following in the Console:

document.querySelector('[name="hp_check"]').value = "I am a bot";

When we clicked Submit, the d365mkt-formsubmit event was triggered and event.preventDefault() successfully cancelled the submission.

Things to consider

While implementing this, there were a few considerations that helped improve the solution:

  • Use a neutral field name such as hp_check instead of generic field name to reduce the chance of browser autofill populating the field.
  • Hide the field using CSS instead of JavaScript to avoid a brief flash of the field while the page is loading.
  • Keep the field unbound, as it is only used for validation.
  • A client-side honeypot helps reduce spam submissions, but it should be considered one layer of protection. For higher security requirements, additional server-side validation can also be implemented. For e.g. if it is bound field on lead than a plugin on pre create operation or if it is unbound we can have plugin against Form Submission table.

Conclusion

A honeypot is a simple but effective technique for reducing automated submissions to Dynamics 365 Customer Insights – Journeys Real-time Marketing Forms.

By using the supported Client-side Extensibility events, the implementation remains lightweight, requires no Dataverse customizations, and can be easily reused across multiple for

References

Real-time Marketing Form Client-side Extensibility

Hope it helps..

Advertisements

Retrieve All One-to-Many Relationships and Cascade Delete Behavior for a Dataverse Table Using C#


While working on a Dynamics 365 Field Service cleanup, we needed to delete a large number of Work Order records.

Before doing so, we wanted to understand exactly what would happen to the related records.

Not every relationship is configured for Cascade Delete. Many use RemoveLink, which doesn’t delete the child records. Instead, Dataverse updates the lookup field to null by removing the relationship.

This behavior is easy to overlook, but it can have unintended consequences. In our case, several integrations were listening for updates on child tables. Deleting a Work Order could therefore generate a large number of update events on related records, potentially triggering unnecessary integration processing.

To identify every table referencing msdyn_workorder along with its delete behavior, I wrote a small console application that retrieves the relationship metadata and exports it to a CSV file.

The application uses the RetrieveEntityRequest message to retrieve all one-to-many relationships for a Dataverse table along with their cascade settings.

static void Main(string[] args)
{
    Console.WriteLine("Connecting...");

    string connectionString =
        ConfigurationManager.AppSettings["ConnectionString"];

    var service = new ServiceClient(connectionString);

    if (!service.IsReady)
    {
        Console.WriteLine("Connection Failed");
        Console.WriteLine(service.LastError);
        Console.ReadLine();
        return;
    }

    Console.WriteLine("Connected Successfully");

    var request = new RetrieveEntityRequest
    {
        LogicalName = "msdyn_workorder",
        EntityFilters = EntityFilters.Relationships
    };

    var response =
        (RetrieveEntityResponse)service.Execute(request);

    var relationships = new List<RelationshipInfo>();

    foreach (var relationship in response.EntityMetadata.OneToManyRelationships)
    {
        relationships.Add(new RelationshipInfo
        {
            SchemaName = relationship.SchemaName,
            ParentTable = relationship.ReferencedEntity,
            ChildTable = relationship.ReferencingEntity,
            LookupAttribute = relationship.ReferencingAttribute,
            DeleteCascade = relationship.CascadeConfiguration.Delete.ToString(),
            AssignCascade = relationship.CascadeConfiguration.Assign.ToString(),
            ShareCascade = relationship.CascadeConfiguration.Share.ToString(),
            ReparentCascade = relationship.CascadeConfiguration.Reparent.ToString()
        });
    }

    Console.WriteLine($"Relationships Found: {relationships.Count}");

    foreach (var relationship in relationships)
    {
        Console.WriteLine(
            $"{relationship.ChildTable} | " +
            $"{relationship.LookupAttribute} | " +
            $"{relationship.DeleteCascade}");
    }

    using (var writer = new StreamWriter("WorkOrderRelationships.csv"))
    using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
    {
        csv.WriteRecords(relationships);
    }

    Console.WriteLine("CSV exported successfully.");
}

RelationshipInfo Class

public class RelationshipInfo
{
    public string SchemaName { get; set; }
    public string ParentTable { get; set; }
    public string ChildTable { get; set; }
    public string LookupAttribute { get; set; }
    public string DeleteCascade { get; set; }
    public string AssignCascade { get; set; }
    public string ShareCascade { get; set; }
    public string ReparentCascade { get; set; }
}

The output :

Why is this useful?

This utility can be useful when we need to:

  • Review cascade behavior before performing bulk deletes.
  • Understand which child tables will be deleted, updated, or left unchanged.
  • Identify RemoveLink relationships that generate automatic update events on child records.
  • Validate plugin and integration behavior before data cleanup activities.
  • Export relationship metadata for documentation or analysis.

The code works for any Dataverse table. Simply replace:

LogicalName = “msdyn_workorder”

with the logical name of the table you want to analyze.

In our case, this quick analysis helped us identify child tables that would receive automatic updates due to RemoveLink relationships, allowing us to ensure those updates did not unnecessarily trigger downstream integrations during the cleanup process.

Hope it helps..

Advertisements

Why SQL4CDS Record Counts May Not Match Advanced Find for Date Filters (Dataverse / Dynamics 365)


While validating some Dynamics 365 Field Service data recently, we came across an interesting scenario where SQL4CDS and Advanced Find returned different record counts even though the date filters appeared to be identical.

At first glance it was surprising to see different record counts being returned despite using what appeared to be the same date range. After investigating further, we found that the difference was related to time zone handling and the behavior of User Local date fields.

In this post, we’ll walk through the issue, explain why it happens, and show how to get matching results between Advanced Find and SQL4CDS.

The Scenario

We had a user in Auckland, New Zealand running the following Advanced Find query against Work Orders.

Date Window Start

  • On or After 01/01/2026
  • On or Before 02/01/2026

Advanced Find returned:

5,755 records

The generated FetchXML looked like this:


To validate the result, we ran the following query in SQL4CDS:

SELECT COUNT(*)
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2026-01-01 00:00:00’
  AND msdyn_datewindowstart <= ‘2026-01-02 00:00:00’;

The results were unexpected.

Query MethodTime Zone UsedResult
Advanced Find (Auckland User)Auckland (NZDT)5,755
SQL4CDSUTC Mode3,027
SQL4CDSLocal Mode (India)3,026

At this point, it was clear that Advanced Find and SQL4CDS were evaluating different date boundaries, even though the filters appeared very similar. The next step was to understand why.

Understanding the Date Window Start Field

The key detail was the configuration of the Date Window Start field.

The Date Window Start field is configured as a Date Only field with User Local behavior.

Although users only see a date value, Dataverse stores an underlying UTC datetime value and performs time zone conversion based on the user’s personal settings.

To better understand what was happening, we queried some of the underlying values directly.

SELECT msdyn_workorderid,
       msdyn_datewindowstart
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2026-01-01 00:00:00’
  AND msdyn_datewindowstart <= ‘2026-01-02 00:00:00’;

When running SQL4CDS in UTC mode, many records had values such as:

2026-01-01 11:00:00

This initially looked unusual because users only see a date value in the application.

However, the explanation becomes clear when we consider the Auckland user’s time zone.

In January, Auckland operates on New Zealand Daylight Time (NZDT), which is UTC+13.

For a User Local Date Only field, Dataverse converts the user’s local date into UTC before storing it.

Date Seen by Auckland UserStored UTC Value
01-Jan-202631-Dec-2025 11:00 UTC
02-Jan-202601-Jan-2026 11:00 UTC
03-Jan-202602-Jan-2026 11:00 UTC

This explains why so many records appear with a value of 11:00 UTC when viewed in SQL4CDS running in UTC mode.

Why Advanced Find Returned More Records

When the Auckland user enters:

01/01/2026
to
02/01/2026

Advanced Find interprets those dates using the user’s personal time zone.

The actual UTC boundaries become:

>= 2025-12-31 11:00:00 UTC
<  2026-01-02 11:00:00 UTC

This represents two complete calendar days for the Auckland user.

Our original SQL4CDS query was searching a different range entirely:

>= 2026-01-01 00:00:00 UTC
<= 2026-01-02 00:00:00 UTC

Although the dates appear similar, the actual UTC boundaries are very different.

Finding the Correct SQL4CDS Query in UTC Mode

To reproduce the Advanced Find results, we converted the Auckland user’s date range into UTC and updated the SQL4CDS query accordingly.

SELECT COUNT(*)
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2025-12-31 11:00:00’
  AND msdyn_datewindowstart <  ‘2026-01-02 11:00:00’;

This returned:

5,755 records

which matched Advanced Find exactly.

What If SQL4CDS Is Running in Local Mode?

The example above used SQL4CDS running in UTC mode. However, SQL4CDS can also be configured to use Local Time mode.

In our scenario, SQL4CDS was running on a machine configured for India Standard Time (IST), which is UTC+5:30.

To match the Advanced Find results in Local Mode, we need to convert the Auckland UTC boundaries into the local time zone used by SQL4CDS.

Earlier we determined that the Auckland user’s date range:

01-Jan-2026 to 02-Jan-2026

corresponds to the following UTC boundaries:

31-Dec-2025 11:00 UTC
to
02-Jan-2026 11:00 UTC

When SQL4CDS is running in Local Mode on an India machine, those UTC values need to be converted to IST.

UTC BoundaryIST Boundary
31-Dec-2025 11:00 UTC31-Dec-2025 16:30 IST
02-Jan-2026 11:00 UTC02-Jan-2026 16:30 IST

The equivalent SQL4CDS query becomes:

SELECT COUNT(*)
FROM msdyn_workorder
WHERE msdyn_datewindowstart >= ‘2025-12-31 16:30:00’
  AND msdyn_datewindowstart <  ‘2026-01-02 16:30:00’;

This query also returned:

5,755 records

matching Advanced Find exactly.

The results can now be summarized as follows:

Validation MethodQuery BoundaryResult
Advanced Find (Auckland User)User Time Zone5,755
SQL4CDS UTC Mode31-Dec-2025 11:00 UTC → 02-Jan-2026 11:00 UTC5,755
SQL4CDS Local Mode (India)31-Dec-2025 16:30 IST → 02-Jan-2026 16:30 IST5,755

References

For a deeper understanding of how SQL4CDS handles date and time values, I highly recommend Mark Carrington’s article:

https://markcarrington.dev/2021/05/21/date-time-handling-in-sql-4-cds

This article explains how SQL4CDS interprets date and time values in both UTC and Local Time modes and was a useful reference while investigating this scenario.

Key Takeaways

The investigation highlighted that there may be three different time zones involved when validating results:

  • The Dataverse user’s personal time zone used by Advanced Find.
  • The SQL4CDS Local Time setting.
  • UTC when SQL4CDS is configured to use UTC mode.

Even when the same date values are entered, the actual UTC range being queried may be different.

For the most reliable comparison:

  1. Identify the time zone of the user who ran Advanced Find.
  2. Convert the date boundaries to UTC.
  3. Run SQL4CDS in UTC mode.
  4. Use explicit UTC values in your query.

We also recommend using an exclusive upper boundary:

WHERE Field >= StartBoundaryUTC
  AND Field < EndBoundaryUTC

instead of:

WHERE Field <= EndOfDay

This avoids potential issues with milliseconds and provides more predictable results.

SQL4CDS can match Advanced Find in either UTC Mode or Local Mode. The important requirement is that the date boundaries represent the same moment in time. We generally prefer UTC Mode because the query behaves consistently regardless of the machine or user executing it.

Hope it helps..

Advertisements