Retrieving dates and times from MS CRM in code

One of the things that often catch the unwary especially when they develop in the UK during the winter (i.e. not when daylight saving time is in operation) is that the dates look fine that the retrieve in code from CRM, but suddenly when the clocks go forward their users report issues that appointments are an hour out. The reason for this is that CRM stores all date/times internally in UTC format, and whenever you store/retrieve a date you should perform a conversion to the users time zone.

The advantage of the system storing time/dates that way is that if you put an appointment for a conference call for 5pm and you are in London, the other parties who may be in San Francisco can check their diaries and will see it correctly scheduled for 9am their time.

To use an example here is the code for a simple plugin that takes a date and returns it in formatted text.

protected override void Execute(CodeActivityContext executionContext)
{
    IWorkflowContext context = executionContext.GetExtension<IWorkflowContext>();
    IOrganizationServiceFactory serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
    IOrganizationService service = serviceFactory.CreateOrganizationService(context.InitiatingUserId);
 
    DateTime dt = Date.Get<DateTime>(executionContext);
    dt = RetrieveLocalTimeFromUTCTime(dt, service, context.InitiatingUserId);
    this.DateOut.Set(executionContext, dt.ToString("dd/mm/yyyy"));
}
 
[Input("Date")]
public InArgument<DateTime> Date { get; set; }
 
 
[Output("DateString")]
public OutArgument<string> DateOut { get; set; }

The code above is pretty straightforward, but the real work is done in the function RetrieveLocalTimeFromUTCTime.

private DateTime RetrieveLocalTimeFromUTCTime(DateTime utcTime, IOrganizationService service, Guid userid)
{
    // query the timezone code ffrom the CRM users table
    var currentUserSettings = service.RetrieveMultiple(new QueryExpression("usersettings")
    {
        ColumnSet = new ColumnSet("localeid", "timezonecode"),
        Criteria = new FilterExpression
        {
            Conditions = { new ConditionExpression("systemuserid", ConditionOperator.Equal,userid) }
        }
    }).Entities[0].ToEntity<Entity>();
    int? timeZoneCode = (int?)currentUserSettings.Attributes["timezonecode"];
    if (!timeZoneCode.HasValue) throw new Exception("Can't find time zone code");
    // then convert the time from UTC to thelocal time as specified by the timezonecode
    var request = new LocalTimeFromUtcTimeRequest
    {
        TimeZoneCode = timeZoneCode.Value,
        UtcTime = utcTime.ToUniversalTime()
    };
    var response = (LocalTimeFromUtcTimeResponse)service.Execute(request);
    return response.LocalTime;
}

This function takes the InitiatingUserId from the execution context of the plugin, and then performs a search in the systemuser table for the time zone for that user. Then it makes a call using the LocalTimeFromUTCTimeRequest/LocalTimeFromUTCResponse function pair to return the correct time. The Guid could come from other sources depending on where the code is to be used.

Forgetting to do this when you write code can lead to some rather embarrassing conversations with your users when the clocks go forward.