Introduction
In one of our earlier posts found here we had explained how the new date/time behaviour can be used to record date/times in the format that you require, instead of having it store the values in UTC all the time. So you can now go and modify the behaviour of an existing date/time field to “Date Only”, suppose you only wanted to store the date part and were not really interested in the time part of the field. But what we read is that the effect of this change only happens moving forward i.e only date values entered or modified after the change in the behaviour would update the field values to store only date removing the time component. The change does not happen retrospectively i.e all existing data in this date field in the database would remain as is.
Example
Suppose the user logged in has the Timezone specified as India Timezone.
This user entered a value in the date/time field prior to updating the field behaviour
Now if we retrieve the value of the field Joining Date of the contact “Jim Glynn”, then we would get the Joining Date as “6/29/2015 7:30:00 PM” as we have specified the Behaviour for this field as User Local and User Local stores the UTC value.
Now if a different user with timezone set to US (EST) logs in they would see the same time as “6/29/2015 3.30:00 PM”. This is because the date is stored in UTC and depending on the user’s timezone, the time is converted and displayed.
Go ahead and now change the behaviour of this field to “Date Only”
Create a new Record
Retrieve the value of this record and it would return it as “6/30/2015 12:00:00 AM”. And in the database this field has been stored without the time component i.e setting the time as 12:00:00AM.
Existing Record
Again retrieve the value of the previous record and you would still see the same date/time value returned “6/29/2015 12:00:00 AM”. When the record was originally created the date entered by 6/30/2015 and I would have expected it to show up as 6/30/2015 without the date part. However, since the change has not been made to the existing records for this field in the database, they are still stored in the original UTC format along with the time. It appears that the UTC date/time is picked and the time part is being set as 12:00:00AM.
How to fix the old data
Even though the SDK API and UI does display the date according to the behaviour provided to avoid any anomalies (as demonstrated above) at the database level where the old records still store the entire date/time in UTC and the new ones only the date with the time part set as 12:00AM, it would be good to have data corrected there to store only date part of the field and have the time reset to 12:00:00 AM for all records.
A new message has been introduced to fix the existing data after a change in the date/time behaviour. Note this message only works when the change has to be done to “Date Only” from any other behaviour.
ConvertDateAndTimeBehavior request has the following required members:
- Attributes: Specifies the collection of entity and attributes to which conversion should apply on.
- ConversionRule: This specifies the conversion rule to be applied for the attribute whose behavior has changed. This is the required parameter.
While specifying the Conversion Rule you can use one the following four members of the DateTimeBehaviorConversionRule class.
- SpecificTimeZone: Converts UTC value to a DateOnly value as per the specified CRM time zone code. In this case, you also need to specify a value for the TimeZoneCode parameter.
- CreatedByTimeZone: Converts UTC value to a DateOnly value that the user who created the record would see in the UI.
- OwnerTimeZone: Converts UTC value to a DateOnly value that the user who owns the record would see in the UI.
- LastUpdatedByTimeZone: Converts UTC value to a DateOnly value that the user who last updated the record would see in the UI.
- TimezoneCode: Specifies CRM time zone code to be used for the conversion. This parameter is required only if SpecificTimeZone is specified in ConversionRule
Here is an example of how to execute this message
ConvertDateAndTimeBehaviorRequest request = new ConvertDateAndTimeBehaviorRequest()
{
Attributes = new EntityAttributeCollection()
{
//specify the collection of entity and attributes
newKeyValuePair<string, StringCollection>(“contact”, new StringCollection(){ “new_joiningdate” })
},
//specify the conversion rule
ConversionRule = DateTimeBehaviorConversionRule.SpecificTimeZone.Value, //Conversion must be performed using the specified timezone
TimeZoneCode = 190 // Time zone code for India in CRM
};
// Execute the request
ConvertDateAndTimeBehaviorResponse response = (ConvertDateAndTimeBehaviorResponse)_service.Execute(request);
This will convert existing values stored for the attribute Joining Date for the previous contact records created before we changed the Behaviour from User local to Date Only according to US Eastern Time.
Whenever we execute the ConvertDateAndTimeBehavior request, a System Job is created with the name as “Convert Date anTimeBehavior”.
This job specifies the following information,
- ConversionRule applied.
- Entity Name of which the date time attribute is converted.
- Names of the attributes processed(for which the conversion rule is applied).
- of rows converted i.e. no. of records of which the DateTime attribute value is changed.
The response that we get when we execute the request returns a JobId of the Async Job created.
//get the Guid of the System Job created
GuidjobId=response.JobId;
Exceptions
If we change the DateTime attribute’s Behaviour from User Local to TimezoneIndependent and we execute the ConvertDateAndTimeBehavior request then it does not convert datetime attribute values.
The async job created specifies the error that the DateTime attribute should have DateOnly behavior.
Result:
After the execution and successful completion of the async job, if you now check the value of the existing data you will see it updated to appropriate date values based on the timezone provided.
No matter what is the timezone setup for the user that is viewing this record, the date would always show up as “6/30/2015”.
Conclusion:
If you have users setup for multiple timezones, it might just make sense to make sure that this request is executed for the date fields that have been set to “Date Only” to reflect the correct date that you want.
Before you move to the next post, have you seen our new Click2Export Solution? A 1 click solution to export reports to Word/Excel and Pdf. Email us on crm@inogic.com for a trial or if you would like to see a live demo.