Single server but multiple timezone database..

[ANS]

Hi,

We need to create different databases for our application serving
different regions (India, US and UK). All this on one database server
(multiple instances is a possiblilty).

Is there a way to instruct SQL server to use a default timezone
(different from the system timezone) for a database or server
instance?

Thanks in advance.

Sriram

[Erland Sommarskog]

ANS (sr***************@gmail.com) writes:

We need to create different databases for our application serving
different regions (India, US and UK). All this on one database server
(multiple instances is a possiblilty).

Is there a way to instruct SQL server to use a default timezone
(different from the system timezone) for a database or server
instance?

You did not say which version of SQL Server you are using. SQL 2005 and
earlier are not time-zone aware at all. You can use getdate() and
getutcdate(), but that's that. In SQL 2008, you can store datetime values
with a timezone offset. But in that case, the clients will have to
provide the timezone. You cannot set a time-zone per database or per
connection. You can set the timezone per server, but only by changing the
timezone in Windows.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>Is there a way to instruct SQL server to use a default timezone (different from the system timezone) for a database or server instance? <<

Standard SQL has been "timezone aware" for awhile. First read the
Standards so you can mimic the right implementation. Keep everything
in UTC, then use VIEWs which reference your timezone table. Yes, it
is painful.

Try to get over to SQL Server 2008, since it does have some support
for ANSI/ISO Standards. It still lacks the ability to put a timezone
declaration on individual columns, however.