CrmDateTime and UTC in Microsoft Dynamics CRM 4.0

eladl — Sun, 13 Mar 2011 21:10:00 GMT

The CrmDateTime in the Microsoft Dynamics CRM 4.0 SDK is a pretty weird data type

on one hand it represents a Date and a time, on the other hand it's Value property is of a string type

One asks himself why not use the known .Net DateTime data type.

One of the reasons (i guess) is that these fields are saved in the [OrganizationName]_MSCRM database synced to UTC

and whenever these fields are requested by any Utility, the CRM adds or subtracts the offset required by the time zone of the user (context) requesting the field.

This way the crm can represent the date, time and offset - the format used for the Value property is yyyy-MM-ddTHH:mm:ss+HH:mm

(Though why not use a data type contains a DateTime member & TimeSpan member?)

Here is a small example:

Let's say i added a contact to the crm exactly in 28/02/2011 16:00 - and my time zone defined as 'Jerusalem (GMT+02:00)'

the CreatedOn field will be saved in the Database as 28/02/2011 14:00, in order to sync to UTC the crm subtracts two hours.

Now let's say a user with 'East coast US(GMT-05:00)' time zone definition opens the contact he will see the date as 28/02/2011 11:00

Note the fact that the time zone definition contains the offset does not necessary means these are the hours we should add/subtract,

the daylight saving issue should also be taken in consideration this means we might need to add/subtract one more hour.

So now we're getting to the interesting part of this post,

Let's say i want to sync a CrmDateTime field to a specific time zone - how (the hell) can i do it?

Server side code

If we use the API provided by microsoft - Microsoft.Crm.Sdk dll, the CrmDateTime provides us with two relevant properties: UniversalTime & UserTime (DateTime data type)

However if you use a web reference to the CrmService - you will not see these properties.....so we got a problem here!

If we use any SQL Engine (NHibernate, Entity Framework, ADO.Net, Linq to SQL,......) - we get the date in UTC.....so problem here too!

The solution is pretty simple,

Using the .Net framework TimeZoneInfo class it can easily be done.

Here is an example showing how to sync date to/from a specific time zone

The result:

So we're OK on the server side code.

What about SQL?

What about SQL reports where we cannot use server side code, how can we manage it?

When you think about it, Microsoft must have created a programmatically solution in the database,

so if we drill down in the database we might find some clues......take a look at these functions under

[OrganizationName]_MSCRM database -> Programmability - > Functions -> Scalar-Valued functions

Each of these gets datetime parameter and returns datetime....just what we were looking for!

Here is the [dbo].[fn_UTCToLocalTime]:

ALTER function [dbo].[fn_UTCToLocalTime]( @UTCTime datetime )
returns datetime
as
begin
      declare @Bias     int
      declare @DaylightBias int
      declare @DaylightYear int
      declare @DaylightMonth int
      declare @DaylightDay    int
      declare @DaylightHour   int
      declare @DaylightMinute int
      declare     @DaylightSecond   int
      declare @DaylightMilliseconds int
      declare @DaylightWeekday      int
      declare @StandardBias int
      declare @StandardYear int
      declare     @StandardMonth    int
      declare @StandardDay    int
      declare @StandardHour int
      declare @StandardMinute int
      declare @StandardSecond int
      declare @StandardMilliseconds int
      declare @StandardWeekday int
      declare @EffectiveDateTime datetime
      declare @ResultDateTime datetime
      declare @FuzzySearchDateTime datetime

      set @DaylightMilliseconds = 0
      set @StandardMilliseconds = 0

      if @UTCTime <= '1/2/1900'
      begin
            return @UTCTime
      end

      select @FuzzySearchDateTime = DATEADD(day, 1, @UTCTime)

      select top 1 @Bias = r.Bias,
            @DaylightBias = r.DaylightBias,
            @DaylightYear = r.DaylightYear,
            @DaylightMonth = r.DaylightMonth,
            @DaylightDay = r.DaylightDay,
            @DaylightHour = r.DaylightHour,
            @DaylightMinute = r.DaylightMinute,
            @DaylightSecond = r.DaylightSecond,
            @DaylightWeekday = r.DaylightDayOfWeek,
            @StandardBias = r.StandardBias,
            @StandardYear = r.StandardYear,
            @StandardMonth = r.StandardMonth,
            @StandardDay = r.StandardDay,
            @StandardHour = r.StandardHour,
            @StandardMinute = r.StandardMinute,
            @StandardSecond = r.StandardSecond,
            @StandardWeekday = r.StandardDayOfWeek,
            @EffectiveDateTime = r.EffectiveDateTime
      from TimeZoneRule as r
      join TimeZoneDefinition as d on d.TimeZoneDefinitionId = r.TimeZoneDefinitionId
      join UserSettingsBase as us on d.TimeZoneCode = us.TimeZoneCode
      where r.EffectiveDateTime <= @FuzzySearchDateTime and us.SystemUserId = dbo.fn_FindUserGuid()
      order by r.TimeZoneRuleVersionNumber desc, r.EffectiveDateTime desc

      select @ResultDateTime = dbo.fn_UTCToTzSpecificLocalTime(@UTCTime,
                                    @Bias,
                                    @DaylightBias,
                                    @DaylightYear,
                                    @DaylightMonth,
                                    @DaylightDay,
                                    @DaylightHour,
                                    @DaylightMinute,
                                    @DaylightSecond,
                                    @DaylightMilliseconds,
                                    @DaylightWeekday,
                                    @StandardBias,
                                    @StandardYear,
                                    @StandardMonth,
                                    @StandardDay,
                                    @StandardHour,
                                    @StandardMinute,
                                    @StandardSecond,
                                    @StandardMilliseconds,
                                    @StandardWeekday)

      if @ResultDateTime < @EffectiveDateTime
      begin
            select top 1 @Bias = r.Bias,
                  @DaylightBias = r.DaylightBias,
                  @DaylightYear = r.DaylightYear,
                  @DaylightMonth = r.DaylightMonth,
                  @DaylightDay = r.DaylightDay,
                  @DaylightHour = r.DaylightHour,
                  @DaylightMinute = r.DaylightMinute,
                  @DaylightSecond = r.DaylightSecond,
                  @DaylightWeekday = r.DaylightDayOfWeek,
                  @StandardBias = r.StandardBias,
                  @StandardYear = r.StandardYear,
                  @StandardMonth = r.StandardMonth,
                  @StandardDay = r.StandardDay,
                  @StandardHour = r.StandardHour,
                  @StandardMinute = r.StandardMinute,
                  @StandardSecond = r.StandardSecond,
                  @StandardWeekday = r.StandardDayOfWeek,
                  @EffectiveDateTime = r.EffectiveDateTime
            from TimeZoneRule as r
            join TimeZoneDefinition as d on d.TimeZoneDefinitionId = r.TimeZoneDefinitionId
            join UserSettingsBase as us on d.TimeZoneCode = us.TimeZoneCode
            where r.EffectiveDateTime <= @ResultDateTime and us.SystemUserId = dbo.fn_FindUserGuid()
            order by r.TimeZoneRuleVersionNumber desc, r.EffectiveDateTime desc

            select @ResultDateTime = dbo.fn_UTCToTzSpecificLocalTime(@UTCTime,
                                          @Bias,
                                          @DaylightBias,
                                          @DaylightYear,
                                          @DaylightMonth,
                                          @DaylightDay,
                                          @DaylightHour,
                                          @DaylightMinute,
                                          @DaylightSecond,
                                          @DaylightMilliseconds,
                                          @DaylightWeekday,
                                          @StandardBias,
                                          @StandardYear,
                                          @StandardMonth,
                                          @StandardDay,
                                          @StandardHour,
                                          @StandardMinute,
                                          @StandardSecond,
                                          @StandardMilliseconds,
                                          @StandardWeekday)
      end
      return @ResultDateTime
end

Take a look at the joins and where conditions:

from TimeZoneRule as r
      join TimeZoneDefinition as d on d.TimeZoneDefinitionId = r.TimeZoneDefinitionId
      join UserSettingsBase as us on d.TimeZoneCode = us.TimeZoneCode
      where r.EffectiveDateTime <= @FuzzySearchDateTime and us.SystemUserId = dbo.fn_FindUserGuid()

The fn_FindUserGuid() function returns the id of the user currently logged on to the crm so the join is by

the user time zone settings.

We can create our own function which gets two parameters:

1. The datetime
2. A time zone name (@timeZoneStandardName in our example)

And change the join to get the TimeZoneRule accordingly, then drop the reference to the SystemUser:

from TimeZoneRule as r
join TimeZoneDefinition as d on (d.TimeZoneDefinitionId = r.TimeZoneDefinitionId and d.StandardName = @timeZoneStandardName)
where r.EffectiveDateTime <= @ResultDateTime

Since we don't want to mess with the crm database we can create the function in our own database and call the CRM database objects with their full name, so the function would look like this:

CREATE FUNCTION [dbo].[fn_UTCToLocalTimeByTimeZoneName](@UTCTime datetime, @timeZoneStandardName nvarchar(50))
returns datetime
as
begin
      declare @Bias     int
      declare @DaylightBias int
      declare @DaylightYear int
      declare @DaylightMonth int
      declare @DaylightDay    int
      declare @DaylightHour   int
      declare @DaylightMinute int
      declare     @DaylightSecond   int
      declare @DaylightMilliseconds int
      declare @DaylightWeekday      int
      declare @StandardBias int
      declare @StandardYear int
      declare     @StandardMonth    int
      declare @StandardDay    int
      declare @StandardHour int
      declare @StandardMinute int
      declare @StandardSecond int
      declare @StandardMilliseconds int
      declare @StandardWeekday int
      declare @EffectiveDateTime datetime
      declare @ResultDateTime datetime
      declare @FuzzySearchDateTime datetime

      select top 1 @Bias = r.Bias,
            @DaylightBias = r.DaylightBias,
            @DaylightYear = r.DaylightYear,
            @DaylightMonth = r.DaylightMonth,
            @DaylightDay = r.DaylightDay,
            @DaylightHour = r.DaylightHour,
            @DaylightMinute = r.DaylightMinute,
            @DaylightSecond = r.DaylightSecond,
            @DaylightWeekday = r.DaylightDayOfWeek,
            @StandardBias = r.StandardBias,
            @StandardYear = r.StandardYear,
            @StandardMonth = r.StandardMonth,
            @StandardDay = r.StandardDay,
            @StandardHour = r.StandardHour,
            @StandardMinute = r.StandardMinute,
            @StandardSecond = r.StandardSecond,
            @StandardWeekday = r.StandardDayOfWeek,
            @EffectiveDateTime = r.EffectiveDateTime
      from [AdventureWorksCycle_MSCRM].[dbo].TimeZoneRule as r
      join [AdventureWorksCycle_MSCRM].[dbo].TimeZoneDefinition as d on (d.TimeZoneDefinitionId = r.TimeZoneDefinitionId
      and d.StandardName = @timeZoneStandardName)
      where r.EffectiveDateTime <= @FuzzySearchDateTime
      order by r.TimeZoneRuleVersionNumber desc, r.EffectiveDateTime desc

      select @ResultDateTime = [AdventureWorksCycle_MSCRM].[dbo].fn_UTCToTzSpecificLocalTime(@UTCTime,
                                    @Bias,
                                    @DaylightBias,
                                    @DaylightYear,
                                    @DaylightMonth,
                                    @DaylightDay,
                                    @DaylightHour,
                                    @DaylightMinute,
                                    @DaylightSecond,
                                    @DaylightMilliseconds,
                                    @DaylightWeekday,
                                    @StandardBias,
                                    @StandardYear,
                                    @StandardMonth,
                                    @StandardDay,
                                    @StandardHour,
                                    @StandardMinute,
                                    @StandardSecond,
                                    @StandardMilliseconds,
                                    @StandardWeekday)

      if @ResultDateTime < @EffectiveDateTime
      begin
            select top 1 @Bias = r.Bias,
                  @DaylightBias = r.DaylightBias,
                  @DaylightYear = r.DaylightYear,
                  @DaylightMonth = r.DaylightMonth,
                  @DaylightDay = r.DaylightDay,
                  @DaylightHour = r.DaylightHour,
                  @DaylightMinute = r.DaylightMinute,
                  @DaylightSecond = r.DaylightSecond,
                  @DaylightWeekday = r.DaylightDayOfWeek,
                  @StandardBias = r.StandardBias,
                  @StandardYear = r.StandardYear,
                  @StandardMonth = r.StandardMonth,
                  @StandardDay = r.StandardDay,
                  @StandardHour = r.StandardHour,
                  @StandardMinute = r.StandardMinute,
                  @StandardSecond = r.StandardSecond,
                  @StandardWeekday = r.StandardDayOfWeek,
                  @EffectiveDateTime = r.EffectiveDateTime
            from [AdventureWorksCycle_MSCRM].[dbo].TimeZoneRule as r
          join [AdventureWorksCycle_MSCRM].[dbo].TimeZoneDefinition as d on (d.TimeZoneDefinitionId = r.TimeZoneDefinitionId
          and d.StandardName = @timeZoneStandardName)
            where r.EffectiveDateTime <= @ResultDateTime
            order by r.TimeZoneRuleVersionNumber desc, r.EffectiveDateTime desc

            select @ResultDateTime = [AdventureWorksCycle_MSCRM].[dbo].fn_UTCToTzSpecificLocalTime(@UTCTime,
                                          @Bias,
                                          @DaylightBias,
                                          @DaylightYear,
                                          @DaylightMonth,
                                          @DaylightDay,
                                          @DaylightHour,
                                          @DaylightMinute,
                                          @DaylightSecond,
                                          @DaylightMilliseconds,
                                          @DaylightWeekday,
                                          @StandardBias,
                                          @StandardYear,
                                          @StandardMonth,
                                          @StandardDay,
                                          @StandardHour,
                                          @StandardMinute,
                                          @StandardSecond,
                                          @StandardMilliseconds,
                                          @StandardWeekday)
      end
      return @ResultDateTime
end

In the above example we call the [AdventureWorksCycle_MSCRM] database from our own

Database, in a similar way we can copy and modify the [dbo].[fn_LocalTimeToUTC] function as well.

And we've got a solution for the SQL side as well.

Microsoft CRM 4.0 Filtered Lookup

motime — Mon, 06 Sep 2010 16:03:00 GMT

Hi,

My name is Moti Mendelovich and I am dev team leader at Agile business solutions.

One of the features that Microsoft CRM 4.0 is missing, is the ability to filter lookups. You can't, for example, set a lookup to account to show only companies that has "agile" on their name.

Over time, several solutions were presented to this problem, but they were all too weak, too obtrusive (harmful to CRM's default behavior - usually by changing aspx file) or ones that costs money

Agile developed a solution that is utilizing the CRM plug-in mechanism. We chose to publish this solution as open source, so the CRM community could enjoy it.

The code (and a zipped version of the files so you will not have to compile it yourself) is available for download at the codeplex site:
http://mscrmfilteredlookup.codeplex.com

Enjoy!

Welcome to Agile IT Blog : MIcrosoft CRM, MSCRM

CrmDateTime and UTC in Microsoft Dynamics CRM 4.0

Microsoft CRM 4.0 Filtered Lookup