Sometimes it might be necessary to change the adjustment of the Time Zone of date-time attributes. This can be done directly the database.
!! Please note that you need a working database backup prior running the following scripts !!
!!! Also you should test this in a test environment first !!!
04/09/14 initial Release
The information of the date-time attribute gets adjusted is been stored in the md_attribute_type table of the LDSD database as md_adjust_for_tz attribute. A value of 1 means time zone adjustment and a value of 0 means no adjustment.
Step by Step:
We need to get the GUID of the attribute first to enter this GUID into the second script which then will toggle the time zone adjustment.
Finding the GUID of the attribute
Let’s assume we are looking into the following attribute 'Invoice Processed' and want to change the time zone adjustment. (This attribute is not part of the OOTB database so you may not find this in your database at all)
To get the GUID we need to adjust the following query depending on what we know about the attribute.
- That the Attribute is part of “IncidentManagement.Closure”
- That the title of the attribute is “Invoice Processed”
- That the type of the attribute is date-time
So we add what we know about the attribute in the following select statement to check if we get only one result-row back. Than we have found our attribute.
!! Please Note. If you do not narrow the filter down to only one attribute, you might end up changing other attributes you didn’t want to change !!
select ct.md_client_class as N'Class Title', at.md_title as N'Attribute Title', md_adjust_for_tz from md_attribute_type as at inner join md_class_type as ct on ct.md_guid = at.md_class_type_guid where at.md_title = 'Invoice Processed' -- Title of attribute and ct.md_client_class like N'%IncidentManagement.Closure' -- Module Note: some Attributes might be in Touchpaper.Framework.Data.DataObject and at.md_data_type = 3 -- datetime attributes only order by ct.md_client_class;
As soon as we narrowed done to just one attribute we copy the filter into the Update statement and run the statement as shown below. This will toggle the time zone adjustment. So if you changed the wrong attribute, you can revert back by running the same update statement again.
with attribute_cte (att_guid) as (select at.md_guid from md_attribute_type as at inner join md_class_type as ct on ct.md_guid = at.md_class_type_guid where at.md_title = 'Invoice Processed' -- Title of attribute and ct.md_client_class like N'%IncidentManagement.Closure' -- Module Note: some Attributes might be in Touchpaper.Framework.Data.DataObject and at.md_data_type = 3) -- datetime attributes only update md_attribute_type set md_adjust_for_tz = case when md_adjust_for_tz = 0 then 1 else 0 end -- will toggle the value where md_guid = (select att_guid from attribute_cte);
The Effects should be visible immediately.