Hi,

We have data warehouse in SQL Server. It host data warehouse for multiple clients based on IDs in tables. For example, Sales table contains sales for all the client diffirentiated by Client ID. These clients are in different timezones. But our server is in EST. When these client run their report they get wrong data & time data. How should we handle this problem?

Please help

Thanks

Prashant

well, at some point you have to alter the dates retrieved by sqlserver to reflect the client's timezone, since whatever's happening now isn't working. i worry about the integrity of the dates you have stored. some questions:

- how did the date get into sqlserver, i.e. what time does that date actually reflect? est? utc? whatever absolute values the clients inserted (yikes!)? say a sale occurred in seattle at 5 p.m. new years eve. is it stored in the database as an absolute 5 p.m., or absolute 8 p.m.? the utc value of 5 p.m. pst? 5 p.m. est?
- where do you lose control of the data? does the date value have to be corrected on the server at the query stage, or can the client software add or subtract a given time offset depending on a runtime date setting that the client specifies?

I might be wrong about this, but maybe I can provide some ideas.

If you notice bulletin board systems (like phpbb, invision, etc), they store all dates as GMT values, then apply algorithms to apply an offset to the user based on their preference.


1. Decide on an "official" time zone for your database. GMT would probably be best since it isn't hampered by daylight savings and works as a unbiased standard.

2. I assume there is a layer of programming between the raw database and the user - If so, create functions within your code that can adjust the GMT stored value to the user's selected time zone and daylight savings preference - This is very simple and takes work away from the database (I assume multiple connections to the database) because the work is offloaded to the client machine.
If you use a web program to access the data from client computers, create php or javascript functions and use cookies to store preferences. Programming the conversions is just as simple, and again relies on the speed of the client computer for conversion.

If you have clients working directly with the database, I don't remember what they are called but you can create some sort of stored queries to do the same, but note that the database is doing all the work in this situation, and may lead to sluggish performance.

In this way, the database doesn't have to grow, doesn't have to do more work, and you can always count on your data to be uniform.

---------------------------------------------------------------------
Actually We are still in the process of building the system. Our clients provide data in Fixed Width Text files. The dates and time does not have timezone information. We load it as it is, therefore data in the table is OK (atleast for the client: For example, 01 Jan 2006 4 PM remains 01 Jan 2006 4 PM) . User runs the report using Business Objects Universe. We have one problem:

1) When user sitting in different timezone, uses system dates or objects based on the that, to pull the data.