TDS Endpoint Queries with Time Zone and User details

A common issue I used to encounter with SQL-based reports for on-premise Dynamics CRM instances was correctly & efficiently filtering on dates.

In the underlying SQL tables, Dataverse stores dates & times in UTC time zone, and this is how they’re indexed. In the views that the TDS Endpoint exposes you can access either this UTC column or one in the user’s local time zone.

ℹ Update: The behaviour of date/time columns has changed in a subsequent update since this post was written. See this updated post for more information

Effect on Performance

As I’ve mentioned previously, filtering and sorting on the user-local time zone columns is very inefficient and should be avoided wherever possible. It’s easy to get timeouts even on quite small datasets.

For sorting it’s easy enough to replace a sort on createdon with one on createdonutc and get the same results. Filtering is a bit more complicated however.

A simple query to get the details of accounts created after a date/time provided by the user might look like:

SELECT name
FROM   account
WHERE  createdon >= @start_date

If I just modify this query to use the createdonutc column and I’m in EST time zone I’ll also get the records created 5 hours before my selected time. If I’m in Sydney I’ll miss the records created for the first 10 hours after that time instead. To make this accurate I need to convert the input to UTC before using it for filtering.

Converting times to UTC

For on-premise SQL reports we could use the function dbo.fn_LocalTimeToUTC(@value) to convert a user-supplied date/time value to UTC. This wasn’t officially supported but worked well as it automatically picked up the appropriate timezone from the user settings. Unfortunately this isn’t accessible using the TDS endpoint.

The native SQL Server version is to use the syntax AT TIME ZONE 'tzname', but again this is not accessible in the TDS endpoint.

Provided we know what the time zone offset is, we can convert values from datetimeoffset to datetime:

declare @createdon datetimeoffset
set @createdon = '2021-01-01 01:00:00 +02:00'
declare @createdonutc datetime
set @createdonutc = convert(datetime, @createdon, 1)

SELECT @createdonutc
-- 2020-12-31 23:00:00

If you’re running queries from a custom app you can use the SDK to convert times to the correct time zone using UtcTimeFromLocalTimeRequest

We can also use Power Automate to convert time zone

Current User Details

Although the TDS endpoint applies security filtering based on the connected user, it doesn’t appear to offer a simple way of finding out who that user is. There were a few unsupported ways of doing this in on-prem SQL reporting days as the user guid would be available from the CONTEXT_INFO() function, but that’s blocked by the TDS endpoint.

If you want to run a query based on the identity of the connected user (a SQL version of My Active Accounts for example) you’ll need to get the user guid externally and pass it in to your query:

var whoami = (WhoAmIResponse) svc.Execute(new WhoAmIRequest());

using (var con = new SqlConnection("..."))
{
  con.Open();

  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText = "SELECT name FROM account WHERE ownerid = @current_user";

    var userParam = cmd.CreateParameter();
    userParam.ParameterName = "@current_user";
    userParam.Value = whoami.UserId;

    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        Console.WriteLine(reader.GetString(0));
      }
    }
  }
}