CrmDateTime and UTC in Microsoft Dynamics CRM 4.0

13 בMarch 2011

אין תגובות

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


      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 [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.


 

הוסף תגובה
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *