We have a gateway running in the pacific time zone with MS SQL and clients running in the eastern time zone . I found that if I create a string that represents a date and use it in the eastern time client with runPrepUpdate the time is not being adjusted for the time zone difference. for example if i set the time for 10 am on the eastern time client runPrepUpdate is storing the time as 10 am in the database when it should be storing it as 7 am.
I found if I use a calendar components date property or use GETDATE() in the query the function adjusts the time correctly.
Is this behavior by design? it would be difficult to find every where in my projects that I used a string instead of an actual date variable when sending data to the database.
We are running version 7.6.3
Thank you,
Yes, this is by design. There is always timezone information with a timestamp (even if it’s just a +0 timezone). When your clients span timezones, you need to start using components and queries that take that into account.
How should we do this for updates and inserts that are from our second Gateway, which is in EST, but there are no components? I have a script which is parsing Xml strings to get a date value. I’ve tried passing the dates to system.db.runPrepUpdate by parsing them into Python dates and Java dates, but they don’t handle the timezones correctly either.
Do I just need to manually adjust the times before sending them?
You have to convert the string into a datetime (or whatever your column type is) inside the query. Something like:SELECT convert(datetime, 'Oct 23 2012 11:01AM')More info/examples here: linesofcode.net/snippets/45