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!
|
|
Date Field Changing
Posted: 03 Apr 09 10:14 AM
|
I have an odd issue occurring with some date fields in one of the account tabs in SLX. Users reported that dates were not what they had set them to a few days ago. After looking at the database and the history on some of the fields (record changes has been set for some) I can see that when certain users access the tab, the dates change.
Observations:
* Dates only change when users view the tab from a terminal server - some users terminal in and access a central SLX installation
* I manually set one of the dates to 2009.4.1 12:00:00 and when the terminal user viewed the account, the date changed to 2009.4.1 05:00:00. I then changed the date in the database to 2009.4.1 04:00:00 and had the user again view the account. The date changed to 2009.3.31 05:00:00
* There are no 'onWhatever' events associated with that tab, or the account details screen that could affect that tab.
I am lead to believe that there is some time zone or date setting on the terminal server SLX installation that is affecting the dates in the system.
Does anyone have any ideas?  |
|
|
|
Re: Date Field Changing
Posted: 03 Apr 09 10:02 PM
|
| It seems that you can reproduce the problem, which is useful. Try running SLX Profiler to determine which erroneous UPDATE statements are issued and you should then be able to track these down in the code. Maybe in the process you'll gain some understanding of why it is happening ... |
|
|
|
Re: Date Field Changing
Posted: 06 Apr 09 4:02 PM
|
Starting in SLX 6.2, all date/time fields entered in SLX are converted to Greenwich time when written to the database, and converted the user's local time when displayed according to the regional settings on the client computer. So SLX on a Terminal Server in a differnet timezone will display a different time and perhaps a different date than a local machine. If any of the machines used have an incorrect time or regional time setting, times and dates can get screwy.
Things to check:
The time and regional settings of the
SQL Server
Saleslgix Server
The terminal server,
Any other client machines.
Also if you have any scripts that set dates in SLX outside if the SLXOLEDB provider, make sure they are setting UCT date/times.
|
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 10:52 AM
|
We are using Windows Server 2003 Terminal Server which has an option called Time Zone Redirection, which identifies someone's time zone as they connect and adjusts their session accordingly. I called Sage and they suggested we turn this option on, although it already is on.
It is very bizarre. I select a date of 3/29/2009 in SLX and save the change. I then query the database and see the field contains 2009/03/29 04:00:00 which would be the GMT time of 3/29/2009 00:00:00 (I am on the East Coast in Daylight savings time so its -4, not -5). But when someone in a different time zone (central timezone in this example) views the tab (simply views it), the date is changed to show "3/28/2009" in the SLX field and prompts me to save my change - what change?! I didn't change anything! If I save it, and query the database I can see the field has changed to a value of 2009/03/28 05:00:00 - it rolls it back to the previous day for some reason.
I checked the time settings on all machines and they are configured correctly. I'm at a loss as to what is going on with these fields. |
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 11:15 AM
|
Unless you define the field as a "Date" field (default is "Date/Time" on the DB Manager), it will always apply GMT conversion.
If you are on the Windows client, typically just changing this field will fix this as SLX won't apply GMT Conversion to it.
For the 7.2 + web client, you probably will need to set the "Timeless" property of the field on the Web Form to true (and if so may also consider setting the "DisplayTime" property to false). |
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 11:39 AM
|
I don't mind that it is doing a GMT conversion, but it should do it correctly.
2009/3/29 04:00:00 GMT == 2009/3/29 00:00:00 EDT
... but ...
2009/3/29 04:00:00 GMT != 2009/3/28 05:00:00 CDT
The problem is that it isn't converting it to CDT correctly. It is rolling the time portion back to 5am on the previous day not 11pm, so when users in EDT look at the field again, its now the 28th for them too. Then if another person in CDT looks at the date, it is rolled back again, and this continues to ping pong back and forth until the day is off by days and weeks.
I can change this particular field to a date field since we don't need the time portion, as you said to do, but how can I guarantee other times and dates which required the time portion aren't being corrupted?
|
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 1:45 PM
|
If dates get set back a whole day, then I'd suspect one of the machines has the wrong date.
Also, simply viewing a record shouldn't update the date fields. Do you have any code in the page that might be altering the date? |
|
|
| |
|
Re: Date Field Changing
Posted: 07 Apr 09 2:02 PM
|
Regarding my previous question, on the 7.2 web client, if you set the Control as "Display Time" = False, it will load the date as "midnight", but will adjust it when it saves.
Because it doesn't have a time, it takes midnight local and then does the GMT Conversion (thus the +5 after saved from CST).
Also, it would cause the date to walk backwards.
We had this issue occur to us because the Timeless property hadn't been set on the Control.
So, if you looked at the dates, they kept on creeping a date back (from the UI) and stored on the DB as GMT from Midnight based on Local Time Zone. |
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 2:12 PM
|
Raul, that is exactly what was happening, except on 6.2.6 Windows client. The database field was a date/time, not date. But the form field was set to just date. So the time portion was being set to midnight. Your previous post about setting the database field type to date and not date/time led me to figure it out, so thanks! I was coming back here to post what I found when I saw you said the same thing! 
But, this has uncovered another problem. The only way I was able to change the field type was to update the SECTABLEDEFS table and change the DATETIMETYPE. However, some of the fields are missing from that table. It seems that previous people poking around SLX have added columns to tables outside of the DB Manager (this SLX environment is old and has had numerous caretakers). I can see the fields in the manager, but of course their type is locked. So a general question is 'how do I handle tables and fields that have been added to the schema outside of DB manager'?
I am hoping you don't tell me to drop and recreate the fields.  |
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 2:21 PM
|
If your system isn't setup for Sync, and you don't have to make a change on those fields, I don't bother adding them to the SECTABLEDEFS.
If I need to Synchronize the table, I would then write down the schema, rename the table, Create a New Table in SLX with the Correct Data and then push the data into the new table (off course, via the provider).
If I don't care about Synchronization, but need to modify the fields, then I do add the Table and its fields into the RESYNCTABLEDEFS and SECTABLEDEFS tables manually.
|
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 2:30 PM
|
We don't sync so that part is out.
The table is already in RESYNCTABLEDEFS, but some of its fields are missing from SECTABELDEFS. FIELDINDEX seems to be the next number in sequence. Is that correct? |
|
|
|
Re: Date Field Changing
Posted: 07 Apr 09 2:43 PM
|
It is.
I do recall that there are some Implications as well around Field Level Security (specially if the fields are missing). For the Most, you could generate a new Index by doing MAX(FIELDINDEX) + 1
|
|
|
|