11/26/2024 8:21:51 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
UTC Conversion Issue
Posted: 30 Oct 06 12:09 PM
|
fiogf49gjkf0d First, let me start out by saying that we are on SalesLogix version 6.2 sp1. Version 6.2 includes the UTC conversion code that stores the date based on the time zone of where the data is being entered.
Now our main office is in Arizona, which is UTC -7. We also have offices in Switzerland and Czech Republic, which is UTC +1. The problem starts to happen when people enter data in Arizona and then it is viewed by people in Switzerland. For example, if a user in Arizona enters a date value of “10/25/2006” with no time stamp, the value will be stored in the database as “2006-10-25 07:00:00.000”. When the user in Switzerland views the same date value, they see “10/24/2006”, because the value is being converted to “2006-10-24 23:00:00.000”.
Now this is not really a bug, but rather it is undesirable. I have been thinking about some work arounds and I was wondering how some you guys have addressed the issue.
One thing you can do it to update the interface to force the user to add a time stamp. Yet this doesn’t totally fix the issue, if the AZ users enter a time before 8am. In addition, it is just an extra step to the users and they won’t like it.
Another thought was to update the data in the database to contain a time stamp of at least UTC +1. However, that doesn’t fix new data coming in.
Another thought was to modify the reports to compensate for the issue, but it still doesn’t fix the data being displayed in the client.
Any advice?
 |
|
|
|
Re: UTC Conversion Issue
Posted: 30 Oct 06 1:10 PM
|
fiogf49gjkf0d Is the data with the date value coming from a customization or an OOTB screen?
If it is coming from a customization, how is the data being added to the database (excuting a SQL insert or update statement, updatable recordset, etc)?
Does the date value refect the true UTC value when viewed directly in SQL (ie: a non-provider connection)?
Is the report that displays the date value a SLX Crystal report (using the SLX Provider)? |
|
|
|
Re: UTC Conversion Issue
Posted: 30 Oct 06 3:05 PM
|
fiogf49gjkf0d The module in question is custom. It contains two methods of data entry. One method is via a legacy data bound form and the second method is via the web client and its aliased fields. Both store the information in the database in the same format.
Yes, the data reflects the true UTC value. Dates entered from AZ are stored in the DB with UTC-7. So a date with no time stamp is midnight -7 for a stored value of 7:00AM.
Yes, the Crystal Reports are run from within the SalesLogix client and use the SLX provider to retrieve the information.
|
|
|
|
Re: UTC Conversion Issue
Posted: 30 Oct 06 5:35 PM
|
fiogf49gjkf0d Forgive my laziness but have you checked the known/resolved issues list for the latest 6.2 service pack? If it was a bug, it should have been addressed in a later release.
If upgrading isn't an option you could always convert DateTime fields to Date or Time fields. Those fields don't do UTC conversion. I believe there's also a way to turn off UTC conversions at the field level, but I'm not exactly sure how that would affect things. For instance, if this is an activity datetime field where sometimes there will be a time entered, you want the conversion to happen. If this is truly just a date only field, then turning UTC conversion off makes sense. |
|
|
|
Re: UTC Conversion Issue
Posted: 30 Oct 06 6:05 PM
|
fiogf49gjkf0d According to SLX Support, this is not a bug. Although to me, it feels like a bug. I would think that the UTC conversion code should only run on dates having to do with scheduling. All other date fields should not care about the time zone and store the value as it was entered. Therefore, I don’t think upgrading with help with this issue, although we are starting to plan a v7 roll after the first service pack is released.
Never the less, if you know of a way to disable this UTC conversion, I’m all ears. A point in the right direction is enough for me.  |
|
|
|
Re: UTC Conversion Issue
Posted: 30 Oct 06 11:24 PM
|
fiogf49gjkf0d If you define the field as a DATE field instead of a DateTime then the timestamp is ignored. Would this solve your problem? |
|
|
|
Re: UTC Conversion Issue
Posted: 31 Oct 06 9:02 AM
|
fiogf49gjkf0d You would need to change the field in the SecTableDefs table via a SQL Script. I spent literally 5-6 months after our upgrade to 6.2.1 dealing with the whole UTC conversion thing. As it was described to me, the OLE DB Provider uses the SecTableDefs DateTimeType field value to determine whether or not to apply the UTC conversion via the SLX user interface. I went through this table and updated all of the custom date fields to have a 'D' value in the DateTimeType. This would state to the provider that this is a date only field and no conversion is necessary. Then I ran scripts to change all of the date fields in the actual table values to strip off the time values, leaving only the dates.
Here are some examples of the scripts that I had to run via WGAdmin to sync out to my remote users.
-- Set Date-Only Fields to Type "D".
-- Run this before the "Remove Time From GMT Fields(Run 2nd).txt"
-- scripts. Execute this through the SalesLogix Administor to
-- make the changes for the central database and all Remote users.
Update sysdba.sectabledefs SET DateTimeType = 'D' where (TableName = 'ACCOUNTINGDOCUMENT' and FieldName = 'DOCUMENTDATE') or (TableName = 'ACCOUNTINGINFO' and FieldName = 'CARDEXPIRATIONDATE') or (TableName = 'ACCOUNTINGINFO' and FieldName = 'LASTACTIVITYDATE') or (TableName = 'ACCOUNTINGINFO' and FieldName = 'LASTINVOICEDATE') or (TableName = 'ACCOUNTINGINFO' and FieldName = 'NOTELASTREVISEDDATE') or (TableName = 'ACCOUNTINGINFO' and FieldName = 'TRANSMITDATE') or (TableName = 'C_ACCOUNT' and FieldName = 'ALF_ANNUAL_DT') or (TableName = 'C_ACCOUNT' and FieldName = 'ALF_DT') or (TableName = 'C_ACCOUNT' and FieldName = 'ALF_MERGER_ACQ_DT')
-- Remove Time from date-only fields to undo unwanted GMT formatting.
-- Run this after the SecTableDefs.DateTimeType field change
-- script, "Set Date-Only Flags(Run 1st).txt", has been run to flag
-- the below fields as date-only, "D".
-- To include Remote databases, run each below Update statement
-- one at a time through the SalesLogix Administrator.
update ACCOUNTINGDOCUMENT set
DOCUMENTDATE = convert(varchar(18), DOCUMENTDATE,101)
update ACCOUNTINGINFO set
CARDEXPIRATIONDATE = convert(varchar(18), CARDEXPIRATIONDATE,101),
LASTACTIVITYDATE = convert(varchar(18), LASTACTIVITYDATE,101),
LASTINVOICEDATE = convert(varchar(18), LASTINVOICEDATE,101),
NOTELASTREVISEDDATE = convert(varchar(18), NOTELASTREVISEDDATE,101),
TRANSMITDATE = convert(varchar(18), TRANSMITDATE,101)
Hope this helps. I was pulling my hair out for months so I know the pain this UTC date thing can cause.  |
|
|
|
Re: UTC Conversion Issue
Posted: 31 Oct 06 9:35 AM
|
fiogf49gjkf0d Wow Sarah, this is exactly what I needed!
This is incredibly useful information for international sites. This should really be posted as an article!
Thank you so very much!
~Jeff |
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|