Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
OldBob-Sco
Starting Member
2 Posts |
 Posted - 2009-02-19 : 13:07:38
|
| Hi everyoneI am currently in the process of planning our migration from storing local datetime to storing utc datetime and was wondering if there is a best practice for implementation. We have a number of clients running our system on SQL2000 and SQL2005 databases and all datetimes are currently storing local time based on the system configuration. My immediate question at the moment is what information to store. There are two thoughts going through my head at the moment.1. Convert existing datetime fields to string and store the date in the YYYY-MM-DDThh:mm:ssTZD formator 2. Add one or two additional columns for each date/time to store the offset and timezone code.There will be additional development required regardless of which way we go, but I was just wondering if there is a perferred method of implementation. ThanksBob |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-02-19 : 13:25:34
|
| Do not modify the column to use a string. That is pointless. Definitely keep the datetime data type.What reason is behind switching them to GMT? Will the client's database ever contain more than one timezone (in the same database)?We store all of our data in GMT as we support customers around the world. We have a TIMEZONE table which contains the needed information to do the conversions and then we also have a table that links a customer to a specific timezone. We have multiple (hundreds and sometimes thousands) customers in each database.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
OldBob-Sco
Starting Member
2 Posts |
Posted - 2009-02-20 : 04:16:21
|
| Thanks for the info Tara."Will the client's database ever contain more than one timezone" - short answer: Yes.Our plans include central databases feeding off multiple clients. Those clients may reside in different timezones (Europe, UK, and US being our main markets at the moment). We have one client who operates from Germany but through virtulisation connects to a UK based server. There are plans to roll this server out to service their UK sites as well.We also interface with other software who supply and expect UTC(GMT) datetime information. We currently have a timezone file in place for this conversion.The information we store is very time critical (clinical information) so I would like to get it right first time :-)I did notice the new fieldtype in SQL2008, but unfortunately that's not an option at the moment.ThanksBob |
|
|
|
|
|
|
UTC Best Practice Question