Hi all.

We have SLX LAN v7.2.1.

I created a grid dynamically and one of the fields is "Last Purchase Date". In the table the "Last Purchase Date" is 01/20/2012, but what appears on the datagrid is 01/19/2012.

Any idea why this is happening and how I can fix this?

Below is the code used for the column

             Set col = .Add(0)

             col.FieldName = "LASTPURCHASEDATE"

             col.Caption = "Last Purchase Date"

             col.Readonly = True

             col.FormatType = ftDateTime

             col.FormatString = "ddddd"

             col.Width = 100

             col.Alignment = 2

             col.HeaderAlignment = 2

Unless you defined the field correctly, SLX will assume that your Date is GMT and will convert it to Local Time Zone.

So, if your Store Date is '2012-02-14 00:00:00.000', the SLX Provider will convert it to local time (e.g. EST 2012-01-13 19:00:00.000').

There is also a detail on SLX storing a 5 second past midnight Time Stamp onto a date to signify that it is a Timeless Date, and in that case the Provider will also not convert the date:

e.g. '2012-02-14 00:00:05.000'  will not be converted to Local Time.

Thanks for the reply Raul.

The Last Purchase Date field is taken from our ERP software, so I don't know how I would define it "correctly". Could you elaborate?

The date is stored in a SQL table in the following format (MM/DD/YYYY).

Well, if that is the case, you may need to convert the date manually to GMT before reading it into SLX.

You may try adding the 5 seconds Time Stamp I mentioned to see if SLX will honor it and not convert it.

Otherwise you will have to figure out the GMT Bias (which is based on the Date value and the user's location) to apply the Conversion.

There may be other ways to bypass it (such as not using the SLX Provider to read the data)....

Just to clarify, the Date/Time Conversion is done at the SLX Provider level, not at the SLX Client or Grid or Column level.

I don't know how to convert the date manually to GMT so is there a way to tell SalesLogix to ignore this date field, in other words, don't convert it just read it as it is in the table?

I figured out something that seems to have worked:

UPDATE sysdba.sectabledefs SET datetimetype = 'D' WHERE fieldname = 'LASTPURCHASEDATE'