My Enterprise Server is sitting in Florida (Eastern Standard Time) and my QA team is in California (Pacific Time). They found if they make an appointment at noon, reload, it shows up at 9am. I checked the database in Florida and it believes the appointment as saved at noon Eastern Standard Time. Somehow, the point of saving and the point of loading the data is missing the time zones.

I’m very surprised no one else has had this problem or anything about it being mentioned in the manual. That usually makes me think I’ve missed something incredibly obvious. My fields are just standard SQL Server date/time fields. I think my issue comes from the fact that I use Stored Procedures to insert/update/delete rows. I suspect the values are beings set in California, shipped to Florida across the time zones and then saved there as if it happened in Florida. When it loads them back up, seems aware that it loaded them in Florida so it should change the values to Califorina time.

I could create some sort of off-set, time zone difference function for date fields way down in the guts of my app before it saves but there must be an easier way. Shouldn’t ES know that the client is in a different time zone and handle this in both directions? I would think the enterprise client would be able to help out too. Any ideas?

I don't think ES is involved with this at all, either in the original problem or a solution to that problem. It just passes data back and forth. It doesn't do anything else.



So, you'll either need to handle this at the client or on the server. Likely it would be easier to handle this at the client (because it knows what its timezone is relative to the known server time zone).



I don't think either the .NET or SQL Server are storing anything about the timezone. Thus, if this is happening, you likely have some other factor affecting this, like a trigger that sets time on server automatically or code that converts the time from server time to client time. Just some guesses, but maybe they'll help you get started.

That was a good point. I did not test if this occurs without ES involved. So, I did that testing. I have VPN access to the server so I created a direct SQL connection from my machine (set to Pacific Time) .  Then, I repeated the test with ES. If you are correct then there should be no difference between using ES and a direct SQL connection.

Unfortunately, that is not the result. Without ES, the program works as expected. If I save an appointment at Noon pacific time, then the server saves the value as 3pm Eastern time. When retrieved, it returns to Noon Pacific time.

That same appointment, if retrieved by ES shows up at 9am. It is clear there is something wrong in the transmission of DateTime values across time zones. I am using encryption and compression but not the custom compression (yet). I can change these values if anyone needs me too, but that will impact a lot of people that I work with so I have to make special plans to do that test.

I think if someone at MicroFour follows these steps, they will see the issue.
1. Create a table with date fields using the DDT.
  a. Make sure you use Store procedures for Insert/Update/Delete
  b. Make sure to use RowVersion for concurrency.
2. Change your Date Time to another time zone but make sure your database server is on another time zone (i.e. don’t do this all on your local machine).
3. Set up an ES connection to your new database, compressed and encrypted using the “out of the box” values.
4. Add new records.
5. Retrieve the records and see what the dates are.

That would mimic my scenario. I bet that you’ll see the issue too.

I just sat down with Ben and talked through this process and you're right, we think there is a disconnect here. Basically, when you send the time value to the server via ES it is sending as UTC ticks, which won't adjust for timezone, whereas our custom serialization on the return trip is sending it over differently (and does account for timezones).  We can't stop just this second and dig into the source to absolutely confirm this, but we're pretty certain this is what is happening to you.

I've added it to the list, and we'll get it fixed for the next update.

I guess I should have said that ES shouldn't do anything to the date/time values. Glad that this is getting tracked down and is out in the open now though!

Yes, this is one of the very many "best things" about working with MicroFour products. When an issue is identified, they really do respond to it. We are very reliant on StrataFrame and we are getting fabulous results in our products from it. That's why I just couldn't believe this issue could be real. I'm glad it was found in our testing cycle and not when we rolled the product out! At the same time, I'm not worried about it being an issue for long.