How does SQL Server handle the transfer of the datetime columns when replicating between timezones?
+1
A:
SQL Server isn't really timezone aware. If you store 2:36 PM in your east coast data ceneter, then replicate the data to your west coast data center, it will still say 2:36 PM.
To get around this type of problem, all of our servers are set to UTC time. This way we always know the date/time in the database is correct to UTC, and if we need to adjust for client display or reporting, we can add/subtract the offset for all date/time values regardless of which server(s) the data came from.
Aaron Bertrand
2010-01-11 18:15:04
Thanks for the quick reply. I am guessing my easiest solution would be to match the timezone as my publisher? (Keep them both EST, instead of EST->CST).
2010-01-11 18:28:11
That is one solution, however if you add new subscriber servers in new data centers in different time zones, then it is going to be a big mess. Ideally you would have UTC (which also isn't subject to daylight savings time) and then the queries/apps could adjust to the clients' preferred time zone. If every single client is in EST and always will be, that'd one thing, but that scenario is pretty rare these days.
Aaron Bertrand
2010-01-11 20:08:45