Gotcha: fn_UTCToLocalTime and fn_LocalTimeToUTC

There are not many scenarios where you would directly operate against an entity base table in CRM. There two scenarios that I can think of are:

  1. Reporting
  2. Data Migration/Integration

Filtered view is the recommended approach when it comes to writing reports for OnPremise CRM server. But if the performance is not really upto the mark, you are forced to either optimise the query by creating indexes/statistics against the correct tables, or directly operate against the base tables.

CRM 2015 Update 1 has introduced the option to create a datetime field as Timezone independent, but in all versions prior to this one, you’ll have to take care of the UTC/Local and Local/UTC conversion, when it comes to datetime fields. CRM does this using fn_UTCToLocalTime and fn_LocalTimeToUTC UDFs that are defined in the MSCRM database.

When you are connected into the SQL database as an Active Directory user, who is not a user in CRM, both these functions will return null. The reason CRM needs to know who the user is to find out the Timezone preferences that are stored again the user’s profile. This is needed to perform the UTC/Local conversion.

In order to understand why this happens, you’ll have to understand how CRM finds out who the current user is.

CRM first tries to match the Active Directory login name of the user against the DomainName column of SystemUser table in MSCRM database. If it can’t find a match, it then tries to match the SystemUserId in the SystemUser table with the context_info on the connection.

Query to understand the behaviour

--user has access to mscrm db, but not a user in CRM
EXECUTE AS user ='CONTOSO\Max.Power'
select dbo.fn_UTCToLocalTime(CreatedOn) from

DECLARE @systemuserid varbinary(128);
select @systemuserid=SystemUserId FROM dbo.systemuser where DomainName='CONTOSO\Alan.Smith'

--impersonate the context of an user who in CRM
SET context_info @systemuserid
select dbo.fn_UTCToLocalTime(CreatedOn) from

--reset context

When you run this query in SSMS, you’ll get something similar to this.

Query Result
Query Result

As you can see the first fn_UTCToLocalTime returned null, but the second one doesn’t, as a different user is impersonated using the context_info. Please be mindful of this behaviour when you are directly working against the base tables.


Opportunity Product Permissions

When an assumption is made that something very basic, can be easily configured using OOB functionalities, that is when I find myself stuck occasionally and having to rethink on how to implement a feature. I recently had one such experience. The requirement was to allow users in certain security role, to delete opportunity products, but not opportunities.My first thought was to use security role, to modify the permissions for opportunity products. To my surprise, I was unable to find opportunity products in Security Role.

It turns out Opportunity Product, Invoice Product, Quote Product and Order Product share a unique trait: they don’t have separate permissions and use the permission of their parent. Such being the limitation, I could implement this using ribbons or plugins. I implemented this using ribbon. Here are the steps

  1. Grant Delete permission on the Opportunity entity for the appropriate security role
  2.  Use Ribbon Workbench to edit the ribbon for opportunity entity
  3. Add a new Enable Rule of type Custom Javascript Rule. I am calling a function in the Javascript webresource
  4.  Add the Enable Rule to the following commands:  
    • Mscrm.DeletePrimaryRecord
    • Mscrm.DeleteSelectedRecord
    • Mscrm.HomePageGrid.DeleteSplitButtonCommand

This change should be done on HomePage, Form and Subgrid ribbons. The code for the getOppDeletePermissionByRole itself is quite simple, as I am using XrmServiceToolkit.

var CVN = window.CVN || {};

CVN.getOppDeletePermissionByRole = function() {
 return !XrmServiceToolkit.Soap.IsCurrentUserRole('1.1 CRM - Base User Role');

window.CVN = CVN;

I did this on a CRM2011 organisation, but the process in same for a CRM2015 organisation. You’ll just be editing the command bar instead of the ribbon. Here are the relevant buttons in CRM2015, whose command you’ll need to edit.

Create New User Error

Yesterday, I got the following error when I tried to add a new user:

You are attempting to create a user with a domain logon that is already used by another user. Select another domain logon and try again.

The strange thing I noticed about the error is that, the user I was trying to add was not already there in CRM. I checked this in the SystemUser table and confirmed that it is definitely not there. After tracing SQL Server, I found CRM uses SystemUserAuthentication table to do this check.

Here is the sequence to events

No user A -> Backup organisation database -> User A created -> Restore organisation db from backup

In my situation, the error started happening after I restored the organisation db from the backup. At the time of the backup, the user I was trying to add had not been created. After the organisation db was restored from backup, the SystemUser table in the organisation database does not contain User A, but the MSCRM_CONFIG does (as per SystemUserAuthentication). This is the root cause of this error message.

The fix was to delete the organisation and re-import the organisation from the Deployment Manager. Once this is done, the new user can be added without any issue. This was the additional step I had to perform after I restored the db from the backup.

GetAttributeValue and null DateTime

Previously, I used to access the value of a property like entity[“attributename”] and cast the result into the appropriate type. The preceding line to this, would always be a check to see if the property exists, as it can cause a KeyNotFoundException, without this check. GetAttributeValue is how I access attribute values these days.

These are some influential posts that made me change my behaviour.
David Berry ->
Guido Preite ->

While this does prevent KeyNotFoundException, it is important to understand the behaviour of GetAttributeValue, w.r.t DateTime. When GetAttributeValue is invoked to retrieve a DateTime attribute, and the value of the attribute is null, it returns a DateTime.MinValue, which is 01/01/0001.

In a scenario where a retrieved value is used to update another record, you’ll have to check if this is DateTime.MinValue before updating, or it will cause an exception like the one below.

The exception thrown is “DateTime is less than minumum[sic] value supported by CrmDateTime. Actual value: 01/01/0001 11:00:00, Minimum value supported: 01/01/1900 00:00:00″.

To prevent this exception, I check if the retrieved DateTime value == DateTime.MinValue, and if so, choose not to update the target property, or set it as null, depending on the requirement. It is also a realisation for me, that CrmDateTime still lives on, somewhere in the Sdk assemblies.

EDIT (29/01/15): Following David’s tip from the comment below, the better approach is to use nullable types with GetAttributeValue, so I should be using DateTime? instead of DateTime.