Our company head office is in LA. But we have global operations. Users
create sales orders and purchase orders across the globe and all data
in stored on the SQL server in LA. But our users wants reporting in
their local time meaning a user raising a sales order on 1st Oct 2006
in Japan at 8 AM local time is still 30th Sept 2006. Users in Japan
want a report to show orders raised on 1st Oct 2006 but the date on the
sales order is 30th Sept 2006 and it will not show the right orders.

How can we provide reporting to accomodate the local time based
reporting to our users.

Thanks
Karen

MS CRM 3.0 has solved this problem by making all the users save their
regional time in the database, and there are functions in their db that does
the conversion between the user's local time and the server time.
You could probably store the location of all the users centrally in your
database, and then do some conversions based on this information. I'm not
sure if you can use RS to get the local system time on the user's computer,
which is why you need to store it "somewhere" in the database.

Kaisa M. Lindahl Lervik

"KarenM" <karenmiddleol (AT) yahoo (DOT) com> wrote

This is problematic if you store only local time and that time zone changes
time during the year (e.g. daylight-savings time, summer time). Even if the
time zone is known (Pacific), it can't be converted to another time zone
unless the GMT bias (-7 or -8) is also stored with the time in the database.
Without the bias being known, you will have time anomalies for orders placed
during the switch to/from PDT/PST; no orders from 2-3am on October 29th,
2006 and twice the normal number from 1-2am on March 11, 2007.

One method to address the issue is to store all times as UTC and convert to
local time for reporting. The conversion can be done in either RS or SQL
but the target local time zone needs to be known in both cases.
Consequently, you'll need to pass the desired reporting time zone as a
report or SQL parameter. If you can't change the current application and
can't determine the GMT bias from database data, you can still convert using
a hard-coded Pacific time algorithm but will have the anomalies described
earlier.

Also, note that a new law goes into effect in the US in 2007. Daylight time
will start on the second Sunday in March and end on the first Sunday in
November

--
Hope this helps.

Dan Guzman
SQL Server MVP

"KarenM" <karenmiddleol (AT) yahoo (DOT) com> wrote