Dynamics Dates Deep Dive

Date(time) fields are a core part of any CRM system, recording when events happened such as records being created/updated or activities being performed. There are several gotchas however which can trip you up in terms of data accuracy or system performance.

Under the covers

All datetime values are stored in the back-end SQL database in UTC. They are converted to user-local times as they are displayed to the user, and converted back to UTC when they are being saved by the user. Whenever you’re using a date(time) value therefore, you must take care to think about the timezone you’re dealing with.

Reading from SQL

If you’re using on-premise (or the fantastic new Data Synchronization solution for online users), you can build reports or other integration systems that read from the SQL database. As I mentioned, all datetime values are ultimately stored as UTC values, but that doesn’t necessarily mean that’s what you’re going to see.

If you read from the unfiltered view (e.g. “account”) or even the base table (e.g. “accountbase”), any datetime value you see will be in UTC. However, if you’re building a report based on a filtered view (e.g. “FilteredAccount”) you can see either the UTC value or the value in the calling user’s local timezone. In the filtered views, fields with names matching the schema names in CRM contain local timezone values, while those with a “utc” suffix on the field name contain (you guessed it!) the UTC value.

So far, so good. If you need a local timezone value, use the filtered view, otherwise the UTC value is available from the base table, unfiltered view or filtered view.

However, if you are using the filtered views and need to filter by a date, you’ll probably want to filter by the user local time. After all, if you are giving the user a parameter to filter their report you probably want that value to make sense to them.

When you run your report, you’ll probably find it takes forever. That’s because the local timezone version of the field you’re filtering by has to be calculated for each row by the SQL server. There’s no possibility to use indexes, so each query has to run a full table scan. As most queries involving date values are something to do with activities, that’s probably a scan of the biggest table in your database, potentially just to retrieve a handful of records.

There is a better way, however. Although the SQL query optimizer can’t do it for you, converting each UTC value in the database to local timezone and comparing it to your filter value is the same as convering your filter value to UTC and comparing the raw values in the database to that. If we do that conversion of the filter value up front and only use the UTC values in the WHERE clause, we can reduce the runtime of the report by an order of magnitude.

If you’re ordering by a datetime field, ensure you use the UTC version for that as well. The end result will be the same but again will likely give you an order of magnitude improvement in run time.

Of course, that leaves the problem of how to take the datetime value from local timezone as entered by the user and convert it to UTC for use in your query. Enter the dbo.fn_LocalTimeToUTC function.

For a real-life example, consider the following query that uses an @StartDate parameter that is entered by the user:

selectcount(*)

from filteredactivitypointer

where scheduledend > @StartDate

If we convert this to:

selectcount(*)

from filteredactivitypointer

where scheduledendutc > dbo.fn_LocalTimeToUTC(@StartDate)

Both give the same result, but while the first version took 4:19 with my test data, the second took less than a second (although I did need to add the recommended index to get this performance).

Reading from the SDK

All DateTime values you get from the SDK (via Retrieve, RetrieveMultiple etc.) are all in UTC. You can convert these to the user local timezone either by using standard DateTime methods such as DateTime.ToLocalTime, assuming the client application is running in an environment with the correct time zone settings, otherwise you can use the LocalTimeFromUtcTimeRequest in the SDK to convert the values according to the time zone associated with the CRM user account.

Updating with the SDK

Any DateTime values you pass to the SDK will be interpreted according to the DateTime.Kind property, which indicates if the value is in the UTC or local time zone. Remember that your local computer time zone may be different to the time zone associated with the user preferences in CRM, and it is the CRM user settings that the SDK takes note of, so if you’re supplying user local times and not getting the results you expect then check that out.

If you use the DateTime.Now or DateTime.UtcNow properties, you get a value that includes the Local or Utc values as you’d expect. However, most other ways of constructing DateTime values leave the Kind property as “Unspecified”, which isn’t very helpful. In these cases, CRM will treat the value as if it was a Local time.