DateTimeEdit Readonly and UTC conversion

If a DateTimeEdit control is set to ReadOnly, user can still modifiy it through the popup date picker and the change WILL be saved. So uncheck “Enabled” is safer.

=============================================

SLOX DB provider has Date and DateTime 2 different data types – even though in SQL server they are all created as DateTime. However SLOX DB provider treats them differently. If it’s a DateTime type, it converts it to UTC time and convert back to local time when getting it. However if it’s a Date type, when saving the data, SLOX DB provider will always use the local date value and either 1) use current local time as time 2) use 00:00:00. (Among other scenarios, if the editing is done directly in a grid view control, 00:00:00 will be used) When retrieving the data, it doesn’t do any conversion – except the Crystal Report, in which case it always try to convert to local time. This is annoying.

Some of the fields should be date only but were created as DateTime type. It created problem when I try to copy that field to another table which I created the field as Date type. The copying was done in SQL so SLOX doesn’t get a chance to do the appropriate conversion. The outcome is some dates are displayed one day ealier in UI. I had to do the UTC to local conversion in SQL query to fix it.

However since it always converts to local time regardless of date type in Crystal Report, I would recommend to always use DateTime type.

===========================================

Internally, SLX stores it’s schema information in SECTableDefs table. This command will list all fields that has Date type:

select * from sysdba.sectabledefs where datetimetype=’D’

‘U’ in above field indicates a DateTime type.

The Architech interface does not allow you to change the data type of an existing field. However here is a workaround in database level:

  1. UPDATE sysdba.sectabledefs SET datetimetype=’U’ WHERE TableName='[my-table]’ AND FieldName='[my-field]’ AND datetimetype=’D’ — this changes the date type
  2. UPDATE [my-table] SET [my-field] = DateAdd(hour, 8, [my-field]) WHERE DatePart(hour, [my-field]) < 8 — this fixes the existing data so that it works for conversion.
Advertisements
This entry was posted in SalesLogix. Bookmark the permalink.

One Response to DateTimeEdit Readonly and UTC conversion

  1. Evan says:

    I agree. It is very annoying.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s