Here are a few more caveats with regards to dates in Saleslogix and DST – experiences from the switch last week-end.

The first versions of Saleslogix 6.2 (6.2.0 to 6.2.1, I think) were plagued with a bug which I call “Saleslogix DST paradox” – if you modified an account during the DST evening, the value saved in the database would appear to be in a different timezone than the one it was actually saved in. For example, if you save the account at 2008-03-08 23:00:00, the GMT value saved to the database (for CST we would be at GMT+6 in winter) would be 2008-03-09 05:00:00. At first glance this appears to be a summer date, thus CDT, thus GMT+5. Therefore Saleslogix reads it as 2008-03-09 00:00:00, which is the paradox. In those versions of Saleslogix this caused the account to be un-saveable. This is no longer the case, thankfully.

However, third party tools are still affected! Here is how you can find out:

• Pick an account in your database, and set its modifydate to 2008-03-10 05:01:00 via SQL: update account set modifydate = ’2008-03-09 05:01:00′ where accountid = ‘AMAZCA10001J’ (replace with your favorite accountid obviously)
• Now in the Saleslogix Administrator, run: select modifydate from account where accountid = ‘AMAZCA10001J’. This will return a modifydate (for Central Time) of: 3/9/2008 12:01:00 AM.
• What is the GMT value of 3/9/2008 00:01:00? This is still CST (CDT only comes into effect at 2am), so GMT-6, so the GMT value is really 06:01:00. This is the first problem.
• Now try: select * from account where accountid=’AMAZCA10001J’ and modifydate=’20080309 00:01:00′. Nothing in the result? Oops?
• Now try: select modifydate, accountid from account where accountid =’AMAZCA10001J’ and modifydate=’20080308 23:01:00′. Anything odd? Yes, we got something different than what we were selecting.

How does Saleslogix do it?

The Saleslogix (network) client itself is not affected by the bug anymore. It turns out, they do the GMT conversion on the client side, instead of having the provider handle it! If you run the Saleslogix profiler you’ll notice they slide in a direct SQL statement (the ones that show in pale green) to retrieve the Modifydate. And to save it they use a timestamp parameter which apparently is not affected by the GMT conversion.

On the web client side, they just gave up on optimistic concurrency locking altogether and run a straight “update account where accountid=…”.

What does this mean for us?

When using the Saleslogix provider, we can’t really rely on the Modifydate as a timestamp value (for optimistic concurrency). But what alternative do we have?

• We could keep an integer timestamp. The problem is it wouldn’t be used or updated by the Saleslogix client itself. So it would really be rather pointless.
• We could do like Saleslogix and implement the conversion on the client side… sounds like a major headache, though.
• We could put some special handling for modifydate when it falls on a DST week-end (not sure how to slide that in)
• We could do like the web client and screw the optimistic concurrency. Not a great loss if you ask me, you usually want the last saved value to stick anyway – I think that is where I am going.