handling different Timezones

I planning on using replication(merge most likely) in an environment where
the subscribers will be in different timezones. Is there way to convert the
datetime fields from the subscriber's to the local database timezone to the
publishers, and vice versa?
Thanks
+---+
William F. Kinsley
Sr. Design Engineer
NextGen Healthcare Info. Sys. Inc
Normally this is done on the application level.
There is no way, other than storing the time as UTC or with time zone
information, that I am aware of doing this.
"William F. Kinsley" <bacardi@.online.nospam> wrote in message
news:%23s5YuzHuEHA.1280@.TK2MSFTNGP10.phx.gbl...
>I planning on using replication(merge most likely) in an environment where
> the subscribers will be in different timezones. Is there way to convert
> the
> datetime fields from the subscriber's to the local database timezone to
> the
> publishers, and vice versa?
> Thanks
> --
> +---+
> William F. Kinsley
> Sr. Design Engineer
> NextGen Healthcare Info. Sys. Inc
>
|||Hi William,
Thanks for your post.
From your descriptions, I understood you would like to know whether there
will be any problems when replicating between different time zones. Have I
understood you? Correct me if I was wrong.
Based on my scope, it won't. We know of many cases where merge is
successfully running across time zones (merge wouldn't be too valuable if
it couldn't ). If you have a look at the usage of getdate in merge's stored
procuedres(identifying those stored procedures would have been useful ),
you will find that merge history tables' use of a datetime (that was
created by getdate) should not be a concern.
Additionally, there is some BOL excerpt for your reference
============
The generation column in these tables acts as a logical clock indicating
when a row was last updated at a given site. Actual datetime values are not
used for marking when changes occur, or deciding conflicts, and there is no
dependence on synchronized clocks between sites. This makes the conflict
detection and resolution algorithms more resilient to time zone differences
and differences between physical clocks on multiple servers. At a given
site, the generation numbers correspond to the order in which changes were
performed by the Merge Agent or by a user at that site.
============
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Thanks for such a detailed reply, this was really not my question, but still
was very helpful. My question is, if I have a user table with a datetime
column, is there a way for the replication process to automatically convert
the datetime from the source databases timezone to the target database's
time zone? I would prefer not to store everything as UTC since I will have
user created reporting on the data.
Thanks
Bill
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:VIGUFEMuEHA.464@.cpmsftngxa10.phx.gbl...
> Hi William,
> Thanks for your post.
> From your descriptions, I understood you would like to know whether there
> will be any problems when replicating between different time zones. Have I
> understood you? Correct me if I was wrong.
> Based on my scope, it won't. We know of many cases where merge is
> successfully running across time zones (merge wouldn't be too valuable if
> it couldn't ). If you have a look at the usage of getdate in merge's
stored
> procuedres(identifying those stored procedures would have been useful ),
> you will find that merge history tables' use of a datetime (that was
> created by getdate) should not be a concern.
> Additionally, there is some BOL excerpt for your reference
> ============
> The generation column in these tables acts as a logical clock indicating
> when a row was last updated at a given site. Actual datetime values are
not
> used for marking when changes occur, or deciding conflicts, and there is
no
> dependence on synchronized clocks between sites. This makes the conflict
> detection and resolution algorithms more resilient to time zone
differences
> and differences between physical clocks on multiple servers. At a given
> site, the generation numbers correspond to the order in which changes were
> performed by the Merge Agent or by a user at that site.
> ============
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
I am sorry for my poor understanding.
If so, I think, as Hilary Cotter had said, there is no such funcationality
provided in SQL Server 2000 and you will have to do it in application
level.
However, it is a great idea to have such expand feature and I'd recommend
that you forward the recommendation to the Microsoft Wish Program:
mswish@.microsoft.com.
Hope the this will be considered in the furture version of SQL Server.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||I was afraid that was going to be the answer, Thank you for your help
Bill
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:qBXAuAmuEHA.2692@.cpmsftngxa10.phx.gbl...
> Hi Bill,
> I am sorry for my poor understanding.
> If so, I think, as Hilary Cotter had said, there is no such funcationality
> provided in SQL Server 2000 and you will have to do it in application
> level.
> However, it is a great idea to have such expand feature and I'd recommend
> that you forward the recommendation to the Microsoft Wish Program:
> mswish@.microsoft.com.
> Hope the this will be considered in the furture version of SQL Server.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Bill,
Welcome!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!